package com.salary.utils;

import com.salary.model.CoefficientEntry;
import com.salary.model.SalesInfo;
import com.salary.model.SalesRecord;
import com.salary.model.SalaryResult;
import com.salary.utils.reader.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;

public class ExcelWriter {


    private static final Logger logger = LoggerFactory.getLogger(ExcelWriter.class);
    // 指定输出Excel文件的保存路径
    private static final String OUTPUT_DIR = "excel";
    /**
     * 导出业务员详细提成数据到Excel文件，便于检查错误
     *
     * @param records 工资记录列表
     * @return 生成的Excel文件路径
     */
    public static String exportDetailedCommission(List<SalesRecord> records) {
        if (records == null || records.isEmpty()) {
            logger.error("没有业务员提成数据可导出");
            return null;
        }

        // 确保输出目录存在
        File dir = new File(OUTPUT_DIR);
        if (!dir.exists()) {
            dir.mkdirs();
        }

        String outputPath = OUTPUT_DIR + File.separator + "业务员提成明细.xlsx";

        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("提成明细");

            // 创建标题样式
            CellStyle headerStyle = workbook.createCellStyle();
            Font headerFont = workbook.createFont();
            headerFont.setBold(true);
            headerStyle.setFont(headerFont);

            // 创建标题行
            Row headerRow = sheet.createRow(0);
            String[] headers = {"业务员", "客户编码", "存货名称", "仓库", "数量(吨)", "吨纸毛利", "提成单价", "行提成", "业务员2", "备注"};

            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(headerStyle);
            }

            // 写入数据
            int rowNum = 1;
            double totalCommission = 0.0;
            Map<String, Double> salespersonCommission = new HashMap<>();

            // 先计算提成单价和行提成
            for (SalesRecord record : records) {
                ExcelUtils.calculateCommissionRate(record);
            }

            // 按业务员名称排序
            List<SalesRecord> sortedRecords = new ArrayList<>(records);
            Collections.sort(sortedRecords, Comparator.comparing(SalesRecord::getSalesperson));

            for (SalesRecord record : sortedRecords) {
                Row row = sheet.createRow(rowNum++);

                row.createCell(0).setCellValue(record.getSalesperson() != null ? record.getSalesperson() : "");
                row.createCell(1).setCellValue(record.getCustomerCode() != null ? record.getCustomerCode() : "");
                row.createCell(2).setCellValue(record.getStockName() != null ? record.getStockName() : "");
                row.createCell(3).setCellValue(record.getWarehouse() != null ? record.getWarehouse() : "");

                Cell quantityCell = row.createCell(4);
                quantityCell.setCellType(CellType.NUMERIC);
                quantityCell.setCellValue(record.getQuantity());

                Cell profitCell = row.createCell(5);
                profitCell.setCellType(CellType.NUMERIC);
                profitCell.setCellValue(record.getPaperProfit());

                Cell rateCell = row.createCell(6);
                rateCell.setCellType(CellType.NUMERIC);
                rateCell.setCellValue(record.getCommissionRate());

                Cell commissionCell = row.createCell(7);
                commissionCell.setCellType(CellType.NUMERIC);
                commissionCell.setCellValue(record.getCommission());

                row.createCell(8).setCellValue(record.getSalesperson2() != null ? record.getSalesperson2() : "");

                // 添加备注信息
                StringBuilder notes = new StringBuilder();

                // 记录判断逻辑
                if (record.getStockName() != null) {
                    if (record.getStockName().contains("幸运鸟") || record.getStockName().contains("新东昇")) {
                        notes.append("幸运鸟/新东昇: 40元/吨");
                        if (record.getCustomerCode() != null && record.getCustomerCode().startsWith("D2")) {
                            notes.append(", D2客户折半: 20元/吨");
                        }
                    } else if (record.getStockName().contains("白鸥")) {
                        notes.append("白鸥: 50元/吨");
                        if (record.getCustomerCode() != null && record.getCustomerCode().startsWith("D2")) {
                            notes.append(", D2客户折半: 25元/吨");
                        }
                    } else {
                        if (record.getPaperProfit() < 150 || (record.getCustomerCode() != null && record.getCustomerCode().startsWith("D2"))) {
                            notes.append("毛利<150或D2客户: 12.5元/吨");
                        } else {
                            notes.append("标准提成: 25元/吨");
                        }
                    }
                }

                // 添加特殊业务员说明（公司资源）
                if ("刘平".equals(record.getSalesperson())) {
                    notes.append(", 特殊业务员刘平: 提成单价×0.6");
                } else if (record.getSalesperson2() != null && record.getSalesperson2().contains("公司资源")) {
                    notes.append(", 业务员2为公司资源: 提成单价×0.6");
                }

                // 添加直送仓说明
                if (record.getWarehouse() != null && record.getWarehouse().contains("直送仓")) {
                    if ("刘涛".equals(record.getSalesperson())) {
                        notes.append(", 直送仓不加价");
                    } else {
                        notes.append(", 直送仓+2.5元/吨");
                    }
                }

                row.createCell(9).setCellValue(notes.toString());

                // 累计提成
                totalCommission += record.getCommission();

                // 按业务员统计
                String salesperson = record.getSalesperson() != null ? record.getSalesperson() : "未知";
                salespersonCommission.put(salesperson,
                        salespersonCommission.getOrDefault(salesperson, 0.0) + record.getCommission());
            }

            // 添加汇总信息
            rowNum += 2; // 空两行
            Row totalRow = sheet.createRow(rowNum++);
            totalRow.createCell(0).setCellValue("总计提成：");
            Cell totalCell = totalRow.createCell(1);
            totalCell.setCellType(CellType.NUMERIC);
            totalCell.setCellValue(totalCommission);

            // 按业务员汇总
            rowNum++;
            Row salespersonTitleRow = sheet.createRow(rowNum++);
            salespersonTitleRow.createCell(0).setCellValue("业务员");
            salespersonTitleRow.createCell(1).setCellValue("提成合计");

            // 对业务员按提成降序排序
            List<Map.Entry<String, Double>> sortedSalespersons = new ArrayList<>(salespersonCommission.entrySet());
            sortedSalespersons.sort(Map.Entry.<String, Double>comparingByValue().reversed());

            for (Map.Entry<String, Double> entry : sortedSalespersons) {
                Row salespersonRow = sheet.createRow(rowNum++);
                salespersonRow.createCell(0).setCellValue(entry.getKey());
                Cell salespersonCommissionCell = salespersonRow.createCell(1);
                salespersonCommissionCell.setCellType(CellType.NUMERIC);
                salespersonCommissionCell.setCellValue(entry.getValue());
            }

            // 自动调整列宽
            for (int i = 0; i < headers.length; i++) {
                sheet.autoSizeColumn(i);
            }

            // 写入文件
            try (FileOutputStream fos = new FileOutputStream(outputPath)) {
                workbook.write(fos);
            }

            logger.info("业务员提成明细已生成: " + outputPath);
            return outputPath;

        } catch (IOException e) {
            logger.error("导出业务员提成明细失败: " + e.getMessage(), e);
            return null;
        }
    }



    /**
     * 将工资计算结果写入模板
     *
     * @param results 工资计算结果列表
     * @param templatePath 模板文件路径，如果为null则使用"输出.xlsx"
     * @param taxFilePath 个税表文件路径，如果为null则不导入个税数据
     * @param socialInsuranceFilePath 社保表文件路径，如果为null则不导入社保数据
     * @param monthlyDeductionFilePath 每月扣款表文件路径，如果为null则不导入扣款数据
     * @param processingGroupFilePath 加工组表格文件路径，如果为null则不导入加工组数据
     * @param attendanceFilePath 考勤表文件路径，如果为null则不导入考勤数据
     * @param assessmentScoreFilePath 考核得分表文件路径，如果为null则不导入考核得分数据
     * @param utilityRentFilePath 水电租表文件路径，如果为null则不导入水电租数据
     * @param coefficientTablePath 系数表文件路径 (新增)
     * @return 生成的Excel文件路径
     */
    public static String writeSalaryResultsToTemplate(
            List<SalaryResult> results,
            String templatePath,
            String taxFilePath,
            String socialInsuranceFilePath,
            String monthlyDeductionFilePath,
            String processingGroupFilePath,
            String attendanceFilePath,
            String assessmentScoreFilePath,
            String utilityRentFilePath,
            String coefficientTablePath) {

        logger.info("开始将工资结果写入模板: {}, 个税表: {}, 社保表: {}, 扣款表: {}, 加工组表: {}, 考勤表: {}, 考核得分表: {}, 水电租表: {}, 系数表: {}",
                templatePath, taxFilePath, socialInsuranceFilePath, monthlyDeductionFilePath, processingGroupFilePath, attendanceFilePath, assessmentScoreFilePath, utilityRentFilePath, coefficientTablePath);

        String templateFilePath = templatePath != null ? templatePath : "输出.xlsx";

        // 获取文件名和路径
        File templateFile = new File(templateFilePath);
        String templateFileName = templateFile.getName();
        String outputDir = templateFile.getParent();
        if (outputDir == null) {
            outputDir = "."; // 如果是相对路径，则使用当前目录
        }

        // 生成输出文件名（在原文件名前加"修改后_"前缀）
        String outputFileName = "修改后_" + templateFileName;
        String outputPath = outputDir + File.separator + outputFileName;

        logger.info("输出文件路径: " + outputPath);

        // 读取个税数据
        Map<String, Double> taxData = new HashMap<>();
        DataReader<Map<String, Double>> reader = new TaxDataReader();
        if (taxFilePath != null && !taxFilePath.isEmpty()) {
            taxData = reader.readData(taxFilePath);
            logger.info("成功读取个税数据，共" + taxData.size() + "条记录");
        }

        // 读取社保数据
        Map<String, Double> socialInsuranceData = new HashMap<>();
        DataReader<Map<String, Double>> socialInsuranceReader = new SocialInsuranceReader();
        if (socialInsuranceFilePath != null && !socialInsuranceFilePath.isEmpty()) {
            socialInsuranceData = socialInsuranceReader.readData(socialInsuranceFilePath);
            logger.info("成功读取社保数据，共" + socialInsuranceData.size() + "条记录");
        }

        // 读取每月扣款数据
        Map<String, Double> deductionData = new HashMap<>();
        DataReader<Map<String, Double>> monthlyDeductionReader = new MonthlyDeductionReader();
        if (monthlyDeductionFilePath != null && !monthlyDeductionFilePath.isEmpty()) {
            deductionData = monthlyDeductionReader.readData(monthlyDeductionFilePath);
            logger.info("成功读取扣款数据，共" + deductionData.size() + "条记录");
        }

        // 读取考核得分数据
        Map<String, Double> assessmentScoreData = new HashMap<>();
        DataReader<Map<String, Double>> assessmentScoreReader = new AssessmentScoreReader();
        if (assessmentScoreFilePath != null && !assessmentScoreFilePath.isEmpty()) {
            assessmentScoreData = assessmentScoreReader.readData(assessmentScoreFilePath);
            logger.info("成功读取考核得分数据，共" + assessmentScoreData.size() + "条记录");
        }

        // 读取考勤数据
        Map<String, Map<String, Double>> attendanceData = new HashMap<>();
        DataReader<Map<String, Map<String, Double>>> attendanceDataReader = new AttendanceDataReader();
        if (attendanceFilePath != null && !attendanceFilePath.isEmpty()) {
            attendanceData = attendanceDataReader.readData(attendanceFilePath);
            logger.info("成功读取考勤数据，共" + attendanceData.size() + "条记录");
        }

        // 读取水电租数据
        Map<String, Double> utilityRentData = new HashMap<>();
        DataReader<Map<String, Double>> utilityRentDataReader = new UtilityRentDataReader();
        if (utilityRentFilePath != null && !utilityRentFilePath.isEmpty()) {
            utilityRentData = utilityRentDataReader.readData(utilityRentFilePath);
            logger.info("成功读取水电房租数据，共" + utilityRentData.size() + "条记录");
        }

        // 读取系数表数据
        Map<String, Double> coefficientData = new HashMap<>();
        DataReader<List<CoefficientEntry>> coefficientDataReader = new CoefficientDataReader();
        if (coefficientTablePath != null && !coefficientTablePath.isEmpty()) {
            List<CoefficientEntry> coefficientEntries = coefficientDataReader.readData(coefficientTablePath);
            for (CoefficientEntry entry : coefficientEntries) {
                coefficientData.put(entry.getName(), entry.getCoefficient());
            }
            logger.info("成功读取系数表数据，共" + coefficientData.size() + "条记录");
        }

        try (FileInputStream fis = new FileInputStream(templateFilePath);
             Workbook workbook = WorkbookFactory.create(fis)) {

            // 获取第一个工作表
            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                logger.error("模板中未找到工作表");
                return null;
            }

            logger.info("成功打开工作表，总行数: " + (sheet.getLastRowNum() + 1));

            // 查找列位置
            int nameColIndex = -1;             // 姓名列
            int totalBonusColIndex = -1;       // 总奖金列
            int taxColIndex = -1;              // 个税列
            int socialInsuranceColIndex = -1;  // 社保列
            int otherDeductionColIndex = -1;   // 应扣部分-其他列
            int bonusReserveColIndex = -1;     // 奖金预留列
            int rewardFundColIndex = -1;       // 奖励基金列
            int positionColIndex = -1;         // 岗位列
            int utilityRentColIndex = -1;      // 水电房租列
            int overtime1_5ColIndex = -1;      // 1.5倍加班列
            int overtime2ColIndex = -1;        // 2倍加班列
            int overtime3ColIndex = -1;        // 3倍加班列
            int basicSalaryColIndex = -1;      // 基本工资列
            int weekdayOvertimeColIndex = -1;  // 平时加班列
            int weekendOvertimeColIndex = -1;  // 双休加班列
            int holidayOvertimeColIndex = -1;  // 节日加班列
            int policyRewardPenaltyColIndex = -1; // 政策奖惩列
            int coefficientColIndex = -1; // 新增：系数列表

            // 查找姓名列和总奖金列
            for (int rowIndex = 0; rowIndex <= 2; rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row == null) continue;

                for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                    Cell cell = row.getCell(colIndex);
                    if (cell == null) continue;

                    String cellValue = DataReader.getCellValueAsString(cell).trim();

                    // 查找姓名列
                    if (cellValue.equals("姓名")) {
                        nameColIndex = colIndex;
                        logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到姓名列: " + cellValue);
                    }

                    // 查找总奖金列 - 更精确的匹配，避免匹配到"奖金预留"
                    if (cellValue.equals("总奖金")) {
                        totalBonusColIndex = colIndex;
                        logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到总奖金列（精确匹配）: " + cellValue);
                    }

                    // 查找个税列
                    if (cellValue.equals("个税")) {
                        taxColIndex = colIndex;
                        logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到个税列: " + cellValue);
                    }

                    // 查找社保列
                    if (cellValue.equals("社保")) {
                        socialInsuranceColIndex = colIndex;
                        logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到社保列: " + cellValue);
                    }

                    // 查找应扣部分-其他列  --todo
                    if (cellValue.contains("其他") && cellValue.contains("应扣")) {
                        otherDeductionColIndex = colIndex;
                        logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到应扣部分-其他列: " + cellValue);
                    }

                    // 查找奖金预留列
                    if ( cellValue.equals("奖金预留")) {
                        bonusReserveColIndex = colIndex;
                        logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到奖金预留列: " + cellValue);
                    }

                    // 查找奖励基金列
                    if (cellValue.equals("奖励基金")) {
                        rewardFundColIndex = colIndex;
                        logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到奖励基金列: " + cellValue);
                    }

                    // 查找水电房租列
                    if (cellValue.equals("水电房租")) {
                        utilityRentColIndex = colIndex;
                        logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到水电房租列: " + cellValue);
                    }

                    // 查找加班列
                    if (cellValue.equals("1.5")) {
                        overtime1_5ColIndex = colIndex;
                        logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到1.5倍加班列: " + cellValue);
                        // 不再继续查找其他匹配项，避免覆盖已找到的值
                        continue;
                    }

                    if (cellValue.equals("2")) {
                        overtime2ColIndex = colIndex;
                        logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到2倍加班列: " + cellValue);
                        // 不再继续查找其他匹配项，避免覆盖已找到的值
                        continue;
                    }

                    if (cellValue.equals("3")) {
                        overtime3ColIndex = colIndex;
                        logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到3倍加班列: " + cellValue);
                        // 不再继续查找其他匹配项，避免覆盖已找到的值
                        continue;
                    }

                    // 查找基本工资列
                    if (cellValue.equals("基本")) {
                        basicSalaryColIndex = colIndex;
                        logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到基本工资列: " + cellValue);
                        // 不再继续查找其他匹配项，避免覆盖已找到的值
                        continue;
                    }

                    // 查找平时加班列
                    if (cellValue.equals("平时加班")) {
                        weekdayOvertimeColIndex = colIndex;
                        logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到平时加班列: " + cellValue);
                        // 不再继续查找其他匹配项，避免覆盖已找到的值
                        continue;
                    }

                    // 查找双休加班列
                    if (cellValue.equals("双休加班")) {
                        weekendOvertimeColIndex = colIndex;
                        logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到双休加班列: " + cellValue);
                        continue;
                    }

                    // 查找节日加班列
                    if (cellValue.equals("节日加班")) {
                        holidayOvertimeColIndex = colIndex;
                        logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到节日加班列: " + cellValue);
                        continue;
                    }

                    // 查找政策奖惩列
                    if (cellValue.contains("政策奖惩")) {
                        policyRewardPenaltyColIndex = colIndex;
                        logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到政策奖惩列: " + cellValue);
                    }

                    // 查找系数列 (新增)
                    if (cellValue.contains("系数")) { // 避免与考勤系数和绩效系数混淆
                        coefficientColIndex = colIndex;
                        logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到系数列: " + cellValue);
                        continue;
                    }
                }
            }

            // 如果没找到，查找L列和M列
            if (bonusReserveColIndex == -1) {
                // 查找每行中有"奖金预留"字样的列，或者L列
                for (int rowIndex = 0; rowIndex <= 5; rowIndex++) {
                    Row row = sheet.getRow(rowIndex);
                    if (row == null) continue;

                    for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                        Cell cell = row.getCell(colIndex);
                        if (cell == null) continue;

                        String cellValue = DataReader.getCellValueAsString(cell).trim();
                        if (cellValue.contains("奖金预留")) {
                            bonusReserveColIndex = colIndex;
                            logger.info("找到奖金预留列: " + colIndex + "(" + (char)('A' + colIndex) + "列)");
                            break;
                        }
                    }

                    if (bonusReserveColIndex != -1) break;
                }

                // 如果仍然找不到，使用截图中的L列位置（索引11）
                if (bonusReserveColIndex == -1) {
                    bonusReserveColIndex = 11; // L列
                    logger.info("未找到奖金预留列，使用默认L列(索引11)");
                }
            }

            if (rewardFundColIndex == -1) {
                // 查找每行中有"奖励基金"字样的列，或者M列
                for (int rowIndex = 0; rowIndex <= 5; rowIndex++) {
                    Row row = sheet.getRow(rowIndex);
                    if (row == null) continue;

                    for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                        Cell cell = row.getCell(colIndex);
                        if (cell == null) continue;

                        String cellValue = DataReader.getCellValueAsString(cell).trim();
                        if (cellValue.contains("奖励基金")) {
                            rewardFundColIndex = colIndex;
                            logger.info("找到奖励基金列: " + colIndex + "(" + (char)('A' + colIndex) + "列)");
                            break;
                        }
                    }

                    if (rewardFundColIndex != -1) break;
                }

                // 如果仍然找不到，使用截图中的M列位置（索引12）
                if (rewardFundColIndex == -1) {
                    rewardFundColIndex = 12; // M列
                    logger.info("未找到奖励基金列，使用默认M列(索引12)");
                }
            }

            // 信息输出
            logger.info("使用姓名列索引: " + nameColIndex + " (第" + (nameColIndex+1) + "列)");
            logger.info("使用总奖金列索引: " + totalBonusColIndex + " (第" + (totalBonusColIndex+1) + "列)");
            logger.info("使用个税列索引: " + taxColIndex + " (第" + (taxColIndex+1) + "列)");
            logger.info("使用社保列索引: " + socialInsuranceColIndex + " (第" + (socialInsuranceColIndex+1) + "列)");
            logger.info("使用应扣部分-其他列索引: " + otherDeductionColIndex + " (第" + (otherDeductionColIndex+1) + "列)");
            logger.info("使用奖金预留列索引: " + bonusReserveColIndex + " (第" + (bonusReserveColIndex+1) + "列)");
            logger.info("使用奖励基金列索引: " + rewardFundColIndex + " (第" + (rewardFundColIndex+1) + "列)");
            logger.info("使用加班列索引 - 1.5倍: " + overtime1_5ColIndex +
                    " (第" + (overtime1_5ColIndex != -1 ? overtime1_5ColIndex+1 : -1) + "列), 2倍: " + overtime2ColIndex +
                    " (第" + (overtime2ColIndex != -1 ? overtime2ColIndex+1 : -1) + "列), 3倍: " + overtime3ColIndex +
                    " (第" + (overtime3ColIndex != -1 ? overtime3ColIndex+1 : -1) + "列)");
            logger.info("使用基本工资列索引: " + basicSalaryColIndex +
                    " (第" + (basicSalaryColIndex != -1 ? basicSalaryColIndex+1 : -1) + "列)");
            logger.info("使用加班工资列索引 - 平时加班: " + weekdayOvertimeColIndex +
                    " (第" + (weekdayOvertimeColIndex != -1 ? weekdayOvertimeColIndex+1 : -1) + "列), " +
                    "双休加班: " + weekendOvertimeColIndex +
                    " (第" + (weekendOvertimeColIndex != -1 ? weekendOvertimeColIndex+1 : -1) + "列), " +
                    "节日加班: " + holidayOvertimeColIndex +
                    " (第" + (holidayOvertimeColIndex != -1 ? holidayOvertimeColIndex+1 : -1) + "列)");
            if (policyRewardPenaltyColIndex != -1) {
                logger.info("将使用政策奖惩列索引: " + policyRewardPenaltyColIndex + " (第" + (policyRewardPenaltyColIndex+1) + "列)");
            }
            if (coefficientColIndex != -1) {
                logger.info("将使用系数列索引: " + coefficientColIndex + " (第" + (coefficientColIndex+1) + "列)");
            }

            // 如果1.5倍列索引为0，可能是错误的默认值，尝试再次搜索
            if (overtime1_5ColIndex == 0) {
                logger.warn("1.5倍列索引为0，尝试重新查找...");

                // 再次尝试查找加班列
                for (int rowIndex = 0; rowIndex <= 5; rowIndex++) {
                    Row row = sheet.getRow(rowIndex);
                    if (row == null) continue;

                    for (int colIndex = 1; colIndex < row.getLastCellNum(); colIndex++) {
                        Cell cell = row.getCell(colIndex);
                        if (cell == null) continue;

                        String cellValue = DataReader.getCellValueAsString(cell).trim();

                        if (cellValue.equals("1") || cellValue.equals("1.5") ||
                                cellValue.contains("1.5倍") || cellValue.contains("1倍半")) {
                            overtime1_5ColIndex = colIndex;
                            logger.info("重新查找到1.5倍加班列，在第" + (rowIndex+1) + "行第" +
                                    (colIndex+1) + "列: " + cellValue);
                            break;
                        }
                    }

                    if (overtime1_5ColIndex > 0) break;
                }
            }

            // 如果没找到，使用默认值
            if (nameColIndex == -1) {
                nameColIndex = 1; // B列
                logger.warn("未在模板中找到姓名列，使用默认B列");
            }

            // 如果没有找到总奖金列，执行更广泛的搜索
            if (totalBonusColIndex == -1) {
                logger.info("未找到精确匹配的总奖金列，执行扩展搜索...");

                // 收集表头所有列并输出，便于调试
                Map<Integer, String> allColumns = new HashMap<>();
                for (int rowIndex = 0; rowIndex <= 5; rowIndex++) {
                    Row row = sheet.getRow(rowIndex);
                    if (row == null) continue;

                    for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                        Cell cell = row.getCell(colIndex);
                        if (cell == null) continue;

                        String cellValue = DataReader.getCellValueAsString(cell).trim();
                        if (!cellValue.isEmpty()) {
                            allColumns.put(colIndex, cellValue);
                            logger.info("表头列[" + rowIndex + "," + colIndex + "]: " + cellValue);
                        }
                    }
                }

                // 尝试查找"总金额"、"总计"等可能的总奖金列
                for (Map.Entry<Integer, String> entry : allColumns.entrySet()) {
                    String columnTitle = entry.getValue();
                    // 精确匹配"总金额"（根据截图红框位置）
                    if (columnTitle.equals("总金额")) {
                        totalBonusColIndex = entry.getKey();
                        logger.info("找到精确匹配的'总金额'列: 在第" + (totalBonusColIndex+1) + "列");
                        break;
                    }
                    // 其他可能的总奖金列
                    else if (columnTitle.contains("总") &&
                            (columnTitle.contains("金额") || columnTitle.contains("金") || columnTitle.contains("计")) &&
                            !columnTitle.contains("预留") &&
                            !columnTitle.contains("实发") &&
                            !columnTitle.contains("应扣")) {

                        totalBonusColIndex = entry.getKey();
                        logger.info("在扩展搜索中找到可能的总奖金列: " + columnTitle + " 在第" + (totalBonusColIndex+1) + "列");
                        break;
                    }
                }

                // 如果仍然找不到，使用默认的O列（索引14，总奖金）
                if (totalBonusColIndex == -1) {
                    totalBonusColIndex = 14; // O列
                    logger.warn("未在模板中找到总奖金列，使用默认O列（总奖金）");
                }
            }

            if (taxColIndex == -1) {
                taxColIndex = 16; // Q列
                logger.warn("未在模板中找到个税列，使用默认Q列（个税）");
            }

            if (socialInsuranceColIndex == -1) {
                socialInsuranceColIndex = 17; // R列
                logger.warn("未在模板中找到社保列，使用默认R列（社保）");
            }

            // 查找应扣部分-其他列
            if (otherDeductionColIndex == -1) {
                // 先查找 "应扣部分" 相关的列
                for (int rowIndex = 0; rowIndex <= 5; rowIndex++) {
                    Row row = sheet.getRow(rowIndex);
                    if (row == null) continue;

                    // 首先尝试查找"应扣部分"标题
                    int deductionSectionIndex = -1;
                    for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                        Cell cell = row.getCell(colIndex);
                        if (cell == null) continue;

                        String cellValue = DataReader.getCellValueAsString(cell).trim();

                        // 查找"应扣部分"标题，可能跨多列的标题
                        if (cellValue.contains("应扣部分")) {
                            deductionSectionIndex = colIndex;
                            logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到'应扣部分'标题: " + cellValue);
                            break;
                        }
                    }

                    // 如果找到了"应扣部分"标题，尝试在下一行查找"其他"
                    if (deductionSectionIndex != -1) {
                        // 在下一行的相应列范围内查找"其他"
                        Row nextRow = sheet.getRow(rowIndex + 1);
                        if (nextRow != null) {
                            for (int colIndex = deductionSectionIndex; colIndex < nextRow.getLastCellNum(); colIndex++) {
                                Cell cell = nextRow.getCell(colIndex);
                                if (cell == null) continue;

                                String cellValue = DataReader.getCellValueAsString(cell).trim();

                                if (cellValue.equals("其他")) {
                                    otherDeductionColIndex = colIndex;
                                    logger.info("在'应扣部分'下找到'其他'列，位于第" + (rowIndex+2) + "行第" + (colIndex+1) + "列");
                                    break;
                                }
                            }
                        }

                        // 如果找到了"其他"列，退出查找
                        if (otherDeductionColIndex != -1) break;
                    }
                }
            }

            // 如果上面的方法没找到，尝试根据截图中的位置查找
            // 从截图看，"其他"列在社保列和实发合计列之间
            if (otherDeductionColIndex == -1) {
                // 找到AK列位置(实发合计列)
                int totalPaymentColIndex = -1;
                for (int rowIndex = 0; rowIndex <= 5; rowIndex++) {
                    Row row = sheet.getRow(rowIndex);
                    if (row == null) continue;

                    for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                        Cell cell = row.getCell(colIndex);
                        if (cell == null) continue;

                        String cellValue = DataReader.getCellValueAsString(cell).trim();

                        if (cellValue.contains("实发合计")) {
                            totalPaymentColIndex = colIndex;
                            logger.info("找到'实发合计'列，位于第" + (rowIndex+1) + "行第" + (colIndex+1) + "列");
                            break;
                        }
                    }

                    if (totalPaymentColIndex != -1) break;
                }

                // 根据截图，"其他"列应该在"社保"列之后，"实发合计"列之前
                if (socialInsuranceColIndex != -1 && totalPaymentColIndex != -1 &&
                        socialInsuranceColIndex < totalPaymentColIndex) {
                    // 查看截图，"其他"列在"公积金"列之后，"实发合计"之前
                    // 在这个区间寻找"其他"标题
                    for (int rowIndex = 0; rowIndex <= 5; rowIndex++) {
                        Row row = sheet.getRow(rowIndex);
                        if (row == null) continue;

                        for (int colIndex = socialInsuranceColIndex + 1; colIndex < totalPaymentColIndex; colIndex++) {
                            Cell cell = row.getCell(colIndex);
                            if (cell == null) continue;

                            String cellValue = DataReader.getCellValueAsString(cell).trim();

                            if (cellValue.equals("其他")) {
                                otherDeductionColIndex = colIndex;
                                logger.info("在社保列和实发合计列之间找到'其他'列，位于第" + (rowIndex+1) + "行第" + (colIndex+1) + "列");
                                break;
                            }
                        }

                        if (otherDeductionColIndex != -1) break;
                    }
                }

                // 如果仍然没找到，但有社保列和公积金列，尝试推断位置
                if (otherDeductionColIndex == -1 && socialInsuranceColIndex != -1) {
                    // 查找公积金列
                    int housingFundColIndex = -1;
                    for (int rowIndex = 0; rowIndex <= 5; rowIndex++) {
                        Row row = sheet.getRow(rowIndex);
                        if (row == null) continue;

                        for (int colIndex = socialInsuranceColIndex + 1; colIndex < row.getLastCellNum(); colIndex++) {
                            Cell cell = row.getCell(colIndex);
                            if (cell == null) continue;

                            String cellValue = DataReader.getCellValueAsString(cell).trim();

                            if (cellValue.contains("公积金")) {
                                housingFundColIndex = colIndex;
                                logger.info("找到'公积金'列，位于第" + (rowIndex+1) + "行第" + (colIndex+1) + "列");
                                break;
                            }
                        }

                        if (housingFundColIndex != -1) break;
                    }

                    // 如果找到了公积金列，则"其他"列应该在其后一列（根据截图）
                    if (housingFundColIndex != -1) {
                        otherDeductionColIndex = housingFundColIndex + 1;
                        logger.info("根据公积金列位置推断'其他'列应该在第" + (otherDeductionColIndex+1) + "列");
                    }
                    // 如果只有社保列，则推断"其他"列在社保列后两列
                    else {
                        otherDeductionColIndex = socialInsuranceColIndex + 2;
                        logger.info("根据社保列位置推断'其他'列应该在第" + (otherDeductionColIndex+1) + "列");
                    }
                }
            }

            // 如果所有方法都找不到，使用默认值AJ列(索引35)，根据截图判断
            if (otherDeductionColIndex == -1) {
                otherDeductionColIndex = 35; // AJ列
                logger.warn("未在模板中找到应扣部分-其他列，使用默认AJ列(索引35)");
            }

            logger.info("将使用姓名列索引: " + nameColIndex + " (第" + (nameColIndex+1) + "列)");
            logger.info("将使用总奖金列索引: " + totalBonusColIndex + " (第" + (totalBonusColIndex+1) + "列)");
            logger.info("将使用个税列索引: " + taxColIndex + " (第" + (taxColIndex+1) + "列)");
            logger.info("将使用社保列索引: " + socialInsuranceColIndex + " (第" + (socialInsuranceColIndex+1) + "列)");
            logger.info("将使用应扣部分-其他列索引: " + otherDeductionColIndex + " (第" + (otherDeductionColIndex+1) + "列)");
            if (policyRewardPenaltyColIndex != -1) {
                logger.info("将使用政策奖惩列索引: " + policyRewardPenaltyColIndex + " (第" + (policyRewardPenaltyColIndex+1) + "列)");
            }
            if (coefficientColIndex != -1) {
                logger.info("将使用系数列索引: " + coefficientColIndex + " (第" + (coefficientColIndex+1) + "列)");
            }

            // 首先处理业务员提成数据
            if (results != null && !results.isEmpty()) {
                // 输出业务员计算结果，方便调试
                for (SalaryResult result : results) {
                    logger.info("准备写入业务员: " + result.getSalesperson() + ", 提成: " + result.getCommission());
                }

                // 从第3行开始（索引为2），前两行是表头
                int startRowIndex = 2;

                // 遍历工资计算结果
                for (SalaryResult result : results) {
                    String salesperson = result.getSalesperson();
                    double commission = result.getCommission();
                    double policyRewardPenaltyValue = result.getPolicyRewardPenalty();

                    // 在模板中查找对应的业务员行
                    boolean found = false;

                    // 从startRowIndex开始遍历所有行
                    for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++) {
                        Row row = sheet.getRow(i);
                        if (row == null) continue;

                        // 获取姓名列
                        Cell nameCell = row.getCell(nameColIndex);
                        if (nameCell == null) continue;

                        String cellValue = DataReader.getCellValueAsString(nameCell).trim();

                        // 精确匹配业务员名称
                        if (cellValue.equals(salesperson)) {
                            logger.info("在第" + (i+1) + "行第" + (nameColIndex+1) + "列找到匹配的业务员: " + cellValue);

                            // 找到对应业务员，将提成写入总奖金列
                            Cell commissionCell = row.getCell(totalBonusColIndex);
                            if (commissionCell == null) {
                                commissionCell = row.createCell(totalBonusColIndex);
                            }

                            // 使用数值格式写入
                            commissionCell.setCellType(CellType.NUMERIC);
                            commissionCell.setCellValue(commission);

                            // 计算并写入奖金预留（总奖金 × -0.2）
                            Cell bonusReserveCell = row.getCell(bonusReserveColIndex);
                            if (bonusReserveCell == null) {
                                bonusReserveCell = row.createCell(bonusReserveColIndex);
                            }
                            bonusReserveCell.setCellType(CellType.NUMERIC);
                            double bonusReserveValue = commission * -0.2; // 负数表示扣除
                            bonusReserveCell.setCellValue(bonusReserveValue);
                            logger.info("写入奖金预留: " + bonusReserveValue + " (总奖金 " + commission + " × -0.2)");

                            // 计算并写入奖励基金（总奖金 × -0.05）
                            Cell rewardFundCell = row.getCell(rewardFundColIndex);
                            if (rewardFundCell == null) {
                                rewardFundCell = row.createCell(rewardFundColIndex);
                            }
                            rewardFundCell.setCellType(CellType.NUMERIC);
                            double rewardFundValue = commission * -0.05; // 负数表示扣除
                            rewardFundCell.setCellValue(rewardFundValue);
                            logger.info("写入奖励基金: " + rewardFundValue + " (总奖金 " + commission + " × -0.05)");

                            // 写入政策奖惩
                            if (policyRewardPenaltyColIndex != -1) {
                                Cell policyCell = row.getCell(policyRewardPenaltyColIndex);
                                if (policyCell == null) {
                                    policyCell = row.createCell(policyRewardPenaltyColIndex);
                                }
                                policyCell.setCellType(CellType.NUMERIC);
                                policyCell.setCellValue(policyRewardPenaltyValue);
                                logger.info("写入政策奖惩: " + policyRewardPenaltyValue + " 到第" + (i+1) + "行第" + (policyRewardPenaltyColIndex+1) + "列");
                            }

                            // 查找当前单元格的列标题，用于确认写入位置
                            String columnTitle = "未知";
                            for (int rowIdx = 0; rowIdx <= 5; rowIdx++) {
                                Row headerRow = sheet.getRow(rowIdx);
                                if (headerRow != null) {
                                    Cell headerCell = headerRow.getCell(totalBonusColIndex);
                                    if (headerCell != null) {
                                        String title = DataReader.getCellValueAsString(headerCell).trim();
                                        if (!title.isEmpty()) {
                                            columnTitle = title;
                                            break;
                                        }
                                    }
                                }
                            }

                            logger.info("成功写入提成值: " + commission + " 到第" + (i+1) + "行第" + (totalBonusColIndex+1) +
                                    "列(" + columnTitle + ")，业务员: " + cellValue);

                            found = true;
                            break;
                        }
                    }

                    // 如果精确匹配未找到，尝试模糊匹配
                    if (!found) {
                        for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++) {
                            Row row = sheet.getRow(i);
                            if (row == null) continue;

                            Cell nameCell = row.getCell(nameColIndex);
                            if (nameCell == null) continue;

                            String cellValue = DataReader.getCellValueAsString(nameCell).trim();

                            // 模糊匹配：任一包含另一个
                            if (cellValue.contains(salesperson) || salesperson.contains(cellValue)) {
                                logger.info("在第" + (i+1) + "行第" + (nameColIndex+1) + "列找到模糊匹配的业务员: " + cellValue + " => " + salesperson);

                                // 找到对应业务员，将提成写入总奖金列
                                Cell commissionCell = row.getCell(totalBonusColIndex);
                                if (commissionCell == null) {
                                    commissionCell = row.createCell(totalBonusColIndex);
                                }

                                // 使用数值格式写入
                                commissionCell.setCellType(CellType.NUMERIC);
                                commissionCell.setCellValue(commission);

                                // 计算并写入奖金预留（总奖金 × -0.2）
                                Cell bonusReserveCell = row.getCell(bonusReserveColIndex);
                                if (bonusReserveCell == null) {
                                    bonusReserveCell = row.createCell(bonusReserveColIndex);
                                }
                                bonusReserveCell.setCellType(CellType.NUMERIC);
                                double bonusReserveValue = commission * -0.2; // 负数表示扣除
                                bonusReserveCell.setCellValue(bonusReserveValue);
                                logger.info("写入奖金预留: " + bonusReserveValue + " (总奖金 " + commission + " × -0.2)");

                                // 计算并写入奖励基金（总奖金 × -0.05）
                                Cell rewardFundCell = row.getCell(rewardFundColIndex);
                                if (rewardFundCell == null) {
                                    rewardFundCell = row.createCell(rewardFundColIndex);
                                }
                                rewardFundCell.setCellType(CellType.NUMERIC);
                                double rewardFundValue = commission * -0.05; // 负数表示扣除
                                rewardFundCell.setCellValue(rewardFundValue);
                                logger.info("写入奖励基金: " + rewardFundValue + " (总奖金 " + commission + " × -0.05)");

                                // 写入政策奖惩
                                if (policyRewardPenaltyColIndex != -1) {
                                    Cell policyCell = row.getCell(policyRewardPenaltyColIndex);
                                    if (policyCell == null) {
                                        policyCell = row.createCell(policyRewardPenaltyColIndex);
                                    }
                                    policyCell.setCellType(CellType.NUMERIC);
                                    policyCell.setCellValue(policyRewardPenaltyValue);
                                    logger.info("写入政策奖惩: " + policyRewardPenaltyValue + " 到第" + (i+1) + "行第" + (policyRewardPenaltyColIndex+1) + "列");
                                }

                                // 查找当前单元格的列标题，用于确认写入位置
                                String columnTitle = "未知";
                                for (int rowIdx = 0; rowIdx <= 5; rowIdx++) {
                                    Row headerRow = sheet.getRow(rowIdx);
                                    if (headerRow != null) {
                                        Cell headerCell = headerRow.getCell(totalBonusColIndex);
                                        if (headerCell != null) {
                                            String title = DataReader.getCellValueAsString(headerCell).trim();
                                            if (!title.isEmpty()) {
                                                columnTitle = title;
                                                break;
                                            }
                                        }
                                    }
                                }

                                logger.info("成功写入提成值: " + commission + " 到第" + (i+1) + "行第" + (totalBonusColIndex+1) +
                                        "列(" + columnTitle + ")，业务员: " + cellValue);

                                found = true;
                                break;
                            }
                        }
                    }

                    if (!found) {
                        logger.warn("未在模板中找到业务员: " + salesperson);
                    }
                }
            }

            // 如果有个税数据，遍历整个模板的姓名列，填写对应的个税数据
            if (!taxData.isEmpty()) {
                logger.info("开始填写个税数据，将检查模板中所有姓名...");

                // 统计匹配的个税数据数量
                int matchCount = 0;

                // 从第3行开始（索引为2），前两行是表头
                int startRowIndex = 2;

                // 遍历模板中的所有行
                for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++) {
                    Row row = sheet.getRow(i);
                    if (row == null) continue;

                    // 获取姓名列
                    Cell nameCell = row.getCell(nameColIndex);
                    if (nameCell == null) continue;

                    String name = DataReader.getCellValueAsString(nameCell).trim();
                    if (name.isEmpty()) continue;

                    // 查找对应的个税数据
                    Double tax = taxData.get(name);
                    if (tax != null) {
                        // 找到对应个税数据，写入个税列
                        Cell taxCell = row.getCell(taxColIndex);
                        if (taxCell == null) {
                            taxCell = row.createCell(taxColIndex);
                        }

                        // 使用数值格式写入
                        taxCell.setCellType(CellType.NUMERIC);
                        taxCell.setCellValue(tax);

                        logger.info("成功写入个税值: " + tax + " 到第" + (i+1) + "行第" + (taxColIndex+1) + "列(个税)，姓名: " + name);
                        matchCount++;
                    } else {
                        // 如果精确匹配未找到，尝试模糊匹配
                        String bestMatch = null;
                        Double bestMatchTax = null;

                        for (Map.Entry<String, Double> entry : taxData.entrySet()) {
                            String taxName = entry.getKey();

                            // 模糊匹配：任一包含另一个
                            if (name.contains(taxName) || taxName.contains(name)) {
                                if (bestMatch == null || taxName.length() > bestMatch.length()) {
                                    // 选择最长的匹配（更可能是准确的）
                                    bestMatch = taxName;
                                    bestMatchTax = entry.getValue();
                                }
                            }
                        }

                        if (bestMatch != null) {
                            // 找到模糊匹配的个税数据，写入个税列
                            Cell taxCell = row.getCell(taxColIndex);
                            if (taxCell == null) {
                                taxCell = row.createCell(taxColIndex);
                            }

                            // 使用数值格式写入
                            taxCell.setCellType(CellType.NUMERIC);
                            taxCell.setCellValue(bestMatchTax);

                            logger.info("成功写入模糊匹配个税值: " + bestMatchTax + " 到第" + (i+1) + "行第" + (taxColIndex+1) +
                                    "列(个税)，姓名: " + name + " => 匹配: " + bestMatch);
                            matchCount++;
                        } else {
                            logger.warn("未找到姓名为 [" + name + "] 的个税数据");
                        }
                    }
                }

                logger.info("个税数据填写完成，共匹配 " + matchCount + " 条数据");
            }

            // 如果有社保数据，遍历整个模板的姓名列，填写对应的社保数据
            if (!socialInsuranceData.isEmpty()) {
                logger.info("开始填写社保数据，将检查模板中所有姓名...");

                // 统计匹配的社保数据数量
                int matchCount = 0;

                // 从第3行开始（索引为2），前两行是表头
                int startRowIndex = 2;

                // 遍历模板中的所有行
                for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++) {
                    Row row = sheet.getRow(i);
                    if (row == null) continue;

                    // 获取姓名列
                    Cell nameCell = row.getCell(nameColIndex);
                    if (nameCell == null) continue;

                    String name = DataReader.getCellValueAsString(nameCell).trim();
                    if (name.isEmpty()) continue;

                    // 查找对应的社保数据
                    Double socialInsurance = socialInsuranceData.get(name);
                    if (socialInsurance != null) {
                        // 找到对应社保数据，写入社保列
                        Cell siCell = row.getCell(socialInsuranceColIndex);
                        if (siCell == null) {
                            siCell = row.createCell(socialInsuranceColIndex);
                        }

                        // 使用数值格式写入
                        siCell.setCellType(CellType.NUMERIC);
                        siCell.setCellValue(socialInsurance);

                        logger.info("成功写入社保值: " + socialInsurance + " 到第" + (i+1) + "行第" + (socialInsuranceColIndex+1) + "列(社保)，姓名: " + name);
                        matchCount++;
                    } else {
                        // 如果精确匹配未找到，尝试模糊匹配
                        String bestMatch = null;
                        Double bestMatchSI = null;

                        for (Map.Entry<String, Double> entry : socialInsuranceData.entrySet()) {
                            String siName = entry.getKey();

                            // 模糊匹配：任一包含另一个
                            if (name.contains(siName) || siName.contains(name)) {
                                if (bestMatch == null || siName.length() > bestMatch.length()) {
                                    // 选择最长的匹配（更可能是准确的）
                                    bestMatch = siName;
                                    bestMatchSI = entry.getValue();
                                }
                            }
                        }

                        if (bestMatch != null) {
                            // 找到模糊匹配的社保数据，写入社保列
                            Cell siCell = row.getCell(socialInsuranceColIndex);
                            if (siCell == null) {
                                siCell = row.createCell(socialInsuranceColIndex);
                            }

                            // 使用数值格式写入
                            siCell.setCellType(CellType.NUMERIC);
                            siCell.setCellValue(bestMatchSI);

                            logger.info("成功写入模糊匹配社保值: " + bestMatchSI + " 到第" + (i+1) + "行第" + (socialInsuranceColIndex+1) +
                                    "列(社保)，姓名: " + name + " => 匹配: " + bestMatch);
                            matchCount++;
                        } else {
                            logger.warn("未找到姓名为 [" + name + "] 的社保数据");
                        }
                    }
                }

                logger.info("社保数据填写完成，共匹配 " + matchCount + " 条数据");
            }

            // 如果有每月扣款数据，填入应扣部分-其他列
            if (!deductionData.isEmpty()) {
                logger.info("开始填写每月扣款数据到应扣部分-其他列，将检查模板中所有姓名...");

                // 统计匹配的扣款数据数量
                int matchCount = 0;

                // 从第3行开始（索引为2），前两行是表头
                int startRowIndex = 2;

                // 遍历模板中的所有行
                for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++) {
                    Row row = sheet.getRow(i);
                    if (row == null) continue;

                    // 获取姓名列
                    Cell nameCell = row.getCell(nameColIndex);
                    if (nameCell == null) continue;

                    String name = DataReader.getCellValueAsString(nameCell).trim();
                    if (name.isEmpty()) continue;

                    // 查找对应的扣款数据
                    Double deduction = deductionData.get(name);
                    if (deduction != null) {
                        // 找到对应扣款数据，写入应扣部分-其他列
                        Cell deductionCell = row.getCell(otherDeductionColIndex);
                        if (deductionCell == null) {
                            deductionCell = row.createCell(otherDeductionColIndex);
                        }

                        // 使用数值格式写入
                        deductionCell.setCellType(CellType.NUMERIC);
                        deductionCell.setCellValue(deduction);

                        logger.info("成功写入扣款值: " + deduction + " 到第" + (i+1) + "行第" + (otherDeductionColIndex+1) + "列(应扣部分-其他)，姓名: " + name);
                        matchCount++;
                    } else {
                        // 如果精确匹配未找到，尝试模糊匹配
                        String bestMatch = null;
                        Double bestMatchDeduction = null;

                        for (Map.Entry<String, Double> entry : deductionData.entrySet()) {
                            String deductionName = entry.getKey();

                            // 模糊匹配：任一包含另一个
                            if (name.contains(deductionName) || deductionName.contains(name)) {
                                if (bestMatch == null || deductionName.length() > bestMatch.length()) {
                                    // 选择最长的匹配（更可能是准确的）
                                    bestMatch = deductionName;
                                    bestMatchDeduction = entry.getValue();
                                }
                            }
                        }

                        if (bestMatch != null) {
                            // 找到模糊匹配的扣款数据，写入应扣部分-其他列
                            Cell deductionCell = row.getCell(otherDeductionColIndex);
                            if (deductionCell == null) {
                                deductionCell = row.createCell(otherDeductionColIndex);
                            }

                            // 使用数值格式写入
                            deductionCell.setCellType(CellType.NUMERIC);
                            deductionCell.setCellValue(bestMatchDeduction);

                            logger.info("成功写入模糊匹配扣款值: " + bestMatchDeduction + " 到第" + (i+1) + "行第" + (otherDeductionColIndex+1) +
                                    "列(应扣部分-其他)，姓名: " + name + " => 匹配: " + bestMatch);
                            matchCount++;
                        } else {
                            logger.warn("未找到姓名为 [" + name + "] 的扣款数据");
                        }
                    }
                }

                logger.info("每月扣款数据填写完成，共匹配 " + matchCount + " 条数据");
            }

            // 在writeSalaryResultsToTemplate方法中添加工龄津贴相关查找和填写逻辑

            // 查找入职日期列和工龄津贴列
            int entryDateColIndex = -1;
            int serviceAllowanceColIndex = -1;

            // 查找"入职日期"列
            for (int rowIndex = 0; rowIndex <= 5; rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row == null) continue;

                for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                    Cell cell = row.getCell(colIndex);
                    if (cell == null) continue;

                    String cellValue = DataReader.getCellValueAsString(cell).trim();

                    if (cellValue.contains("入职日期")) {
                        entryDateColIndex = colIndex;
                        logger.info("找到'入职日期'列，位于第" + (rowIndex+1) + "行第" + (colIndex+1) + "列");
                        break;
                    }
                }

                if (entryDateColIndex != -1) break;
            }

            // 查找"工龄津贴"列
            for (int rowIndex = 0; rowIndex <= 5; rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row == null) continue;

                for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                    Cell cell = row.getCell(colIndex);
                    if (cell == null) continue;

                    String cellValue = DataReader.getCellValueAsString(cell).trim();

                    if (cellValue.contains("工龄津贴")) {
                        serviceAllowanceColIndex = colIndex;
                        logger.info("找到'工龄津贴'列，位于第" + (rowIndex+1) + "行第" + (colIndex+1) + "列");
                        break;
                    }
                }

                if (serviceAllowanceColIndex != -1) break;
            }

            // 根据截图确定列的位置
            if (entryDateColIndex == -1) {
                // 查找"D"列的"入职日期"
                logger.info("尝试查找'入职日期'列");
                for (int rowIndex = 0; rowIndex <= 5; rowIndex++) {
                    Row row = sheet.getRow(rowIndex);
                    if (row == null) continue;

                    for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                        Cell cell = row.getCell(colIndex);
                        if (cell == null) continue;

                        String cellValue = DataReader.getCellValueAsString(cell).trim();
                        if (cellValue.contains("入职")) {
                            entryDateColIndex = colIndex;
                            logger.info("找到'入职日期'列，位于第" + (rowIndex+1) + "行第" + (colIndex+1) + "列");
                            break;
                        }
                    }

                    if (entryDateColIndex != -1) break;
                }
            }

            if (serviceAllowanceColIndex == -1) {
                // 扩展搜索范围，查找完整的表头，包括工龄津贴
                logger.info("扩大搜索范围，尝试找到'工龄津贴'列");

                // 记录所有找到的标题列，用于辅助判断
                Map<Integer, String> columnTitles = new HashMap<>();

                // 首先，扫描所有顶部行并记录标题
                for (int rowIndex = 0; rowIndex <= 5; rowIndex++) {
                    Row row = sheet.getRow(rowIndex);
                    if (row == null) continue;

                    for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                        Cell cell = row.getCell(colIndex);
                        if (cell == null) continue;

                        String cellValue = DataReader.getCellValueAsString(cell).trim();
                        if (!cellValue.isEmpty()) {
                            columnTitles.put(colIndex, cellValue);
                            // 打印所有列标题以便调试
                            logger.info("找到列标题：第" + (rowIndex+1) + "行第" + (colIndex+1) +
                                    "列(" + (char)('A' + colIndex) + "列)：" + cellValue);

                            // 尝试更多可能的匹配方式找工龄津贴
                            if (cellValue.contains("工龄津贴") ||
                                    cellValue.equals("工龄津贴") ||
                                    (cellValue.contains("工龄") && cellValue.contains("津贴")) ||
                                    cellValue.equals("工龄") ||
                                    cellValue.equals("津贴") ||
                                    cellValue.contains("工龄补贴")) {

                                serviceAllowanceColIndex = colIndex;
                                logger.info("找到工龄津贴列，位于第" + (rowIndex+1) + "行第" + (colIndex+1) +
                                        "列(" + (char)('A' + colIndex) + "列)，标题为：" + cellValue);
                                break;
                            }
                        }
                    }

                    if (serviceAllowanceColIndex != -1) break;
                }

                // 如果直接查找没找到，尝试从截图分析
                if (serviceAllowanceColIndex == -1) {
                    logger.info("尝试根据截图分析寻找工龄津贴列");
                    // 根据截图中的位置判断，工龄津贴列应该在"奖金"相关列的附近

                    // 查找总奖金列或相关列
                    Integer bonusColIndex = null;
                    for (Map.Entry<Integer, String> entry : columnTitles.entrySet()) {
                        String title = entry.getValue();
                        if (title.contains("总奖金") ||
                                title.contains("奖金") ||
                                title.equals("奖金") ||
                                title.contains("提成") ||
                                title.contains("奖励")) {

                            bonusColIndex = entry.getKey();
                            logger.info("找到奖金相关列，位于第" + (bonusColIndex+1) + "列(" +
                                    (char)('A' + bonusColIndex) + "列)，标题为：" + title);
                            break;
                        }
                    }

                    if (bonusColIndex != null) {
                        // 根据截图，工龄津贴可能在奖金列的附近
                        for (int offset = -3; offset <= 3; offset++) {
                            int checkCol = bonusColIndex + offset;
                            if (checkCol >= 0 && columnTitles.containsKey(checkCol)) {
                                String title = columnTitles.get(checkCol);
                                logger.info("检查奖金列附近：第" + (checkCol+1) + "列(" +
                                        (char)('A' + checkCol) + "列)：" + title);
                            }
                        }

                        // 检查工资表标准字段列顺序，通常工龄津贴在基本工资、奖金相关的列旁边
                        // 先尝试奖金列右侧的列
                        serviceAllowanceColIndex = bonusColIndex + 1;
                        logger.info("推断工龄津贴列位于奖金列右侧，第" + (serviceAllowanceColIndex+1) +
                                "列(" + (char)('A' + serviceAllowanceColIndex) + "列)");
                    }
                }
            }

            // 如果仍然找不到，使用默认值
            if (entryDateColIndex == -1) {
                entryDateColIndex = 3; // D列
                logger.warn("未找到入职日期列，使用默认的D列(索引3)");
            }

            if (serviceAllowanceColIndex == -1) {
                // 查看N列标题，因为截图显示可能在N列
                String nColumnTitle = "";
                Row headerRow = sheet.getRow(1); // 通常第2行有标题
                if (headerRow != null && headerRow.getLastCellNum() > 13) {
                    Cell nCell = headerRow.getCell(13); // N列索引为13
                    if (nCell != null) {
                        nColumnTitle = DataReader.getCellValueAsString(nCell).trim();
                        logger.info("N列(索引13)标题为：" + nColumnTitle);
                    }
                }

                // 使用N列作为默认值，因为根据截图它很可能是工龄津贴列
                serviceAllowanceColIndex = 13; // N列
                logger.warn("未找到工龄津贴列，使用默认的N列(索引13)");
            }

            logger.info("将使用入职日期列索引: " + entryDateColIndex + " (第" + (entryDateColIndex+1) + "列)");
            logger.info("将使用工龄津贴列索引: " + serviceAllowanceColIndex + " (第" + (serviceAllowanceColIndex+1) + "列)");

            // 如果找到了入职日期列和工龄津贴列，计算并填写工龄津贴
            if (entryDateColIndex != -1 && serviceAllowanceColIndex != -1) {
                logger.info("开始计算工龄津贴...");

                // 从第3行开始（索引为2），前两行是表头
                int startRowIndex = 2;

                // 计算工龄津贴的员工数量
                int calculatedCount = 0;

                // 遍历模板中的所有行
                for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++) {
                    Row row = sheet.getRow(i);
                    if (row == null) continue;

                    // 获取姓名列
                    Cell nameCell = row.getCell(nameColIndex);
                    if (nameCell == null || DataReader.getCellValueAsString(nameCell).trim().isEmpty()) continue;

                    // 获取入职日期
                    Cell entryDateCell = row.getCell(entryDateColIndex);
                    if (entryDateCell == null) continue;

                    String entryDateStr = DataReader.getCellValueAsString(entryDateCell).trim();
                    if (entryDateStr.isEmpty()) continue;

                    // 添加更多日志以便调试
                    logger.info("正在处理员工入职日期，原始单元格类型: " + entryDateCell.getCellType() +
                            ", 原始值: " + entryDateStr);

                    // 如果是日期类型单元格，直接获取日期值
                    if (entryDateCell.getCellType() == CellType.NUMERIC && DateUtil.isCellDateFormatted(entryDateCell)) {
                        Date dateValue = entryDateCell.getDateCellValue();
                        entryDateStr = dateValue.toString();
                        logger.info("单元格是日期类型，获取到的日期值: " + entryDateStr);
                    }

                    // 计算工龄津贴
                    double serviceAllowance = ExcelUtils.calculateServiceAllowance(entryDateStr);

                    // 如果工龄津贴大于0，填入工龄津贴列
                    if (serviceAllowance > 0) {
                        Cell serviceAllowanceCell = row.getCell(serviceAllowanceColIndex);
                        if (serviceAllowanceCell == null) {
                            serviceAllowanceCell = row.createCell(serviceAllowanceColIndex);
                        }

                        // 使用数值格式写入
                        serviceAllowanceCell.setCellType(CellType.NUMERIC);
                        serviceAllowanceCell.setCellValue(serviceAllowance);

                        String employeeName = DataReader.getCellValueAsString(nameCell).trim();
                        logger.info("为员工 " + employeeName + " 计算工龄津贴: " + serviceAllowance +
                                "元 (入职日期: " + entryDateStr + ")");

                        calculatedCount++;
                    }
                }

                logger.info("工龄津贴计算完成，共为 " + calculatedCount + " 名员工计算了工龄津贴");
            } else {
                logger.warn("未找到有效的入职日期列或工龄津贴列，跳过工龄津贴计算");
            }

            // 如果有加工组数据，处理加工组数据并更新总奖金
            if (processingGroupFilePath != null && !processingGroupFilePath.isEmpty()) {
                logger.info("开始处理加工组数据...");
                boolean processingSuccess = ExcelUtils.processProcessingGroupData(processingGroupFilePath, sheet, nameColIndex, totalBonusColIndex);
                if (processingSuccess) {
                    logger.info("成功处理加工组数据");
                } else {
                    logger.error("处理加工组数据失败");
                }
            }

            // 如果有水电租数据，遍历整个模板的姓名列，填写对应的水电房租数据
            if (!utilityRentData.isEmpty()) {
                logger.info("开始填写水电房租数据，将检查模板中所有姓名...");

                // 统计匹配的水电房租数据数量
                int matchCount = 0;

                // 从第3行开始（索引为2），前两行是表头
                int startRowIndex = 2;

                // 遍历模板中的所有行
                for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++) {
                    Row row = sheet.getRow(i);
                    if (row == null) continue;

                    // 获取姓名列
                    Cell nameCell = row.getCell(nameColIndex);
                    if (nameCell == null) continue;

                    String name = DataReader.getCellValueAsString(nameCell).trim();
                    if (name.isEmpty()) continue;

                    // 查找对应的水电房租数据
                    Double utilityRent = utilityRentData.get(name);
                    if (utilityRent != null) {
                        // 找到对应水电房租数据，写入水电房租列
                        Cell urCell = row.getCell(utilityRentColIndex);
                        if (urCell == null) {
                            urCell = row.createCell(utilityRentColIndex);
                        }

                        // 使用数值格式写入
                        urCell.setCellType(CellType.NUMERIC);
                        urCell.setCellValue(utilityRent);

                        logger.info("成功写入水电房租值: " + utilityRent + " 到第" + (i+1) + "行第" +
                                (utilityRentColIndex+1) + "列(水电房租)，姓名: " + name);
                        matchCount++;
                    } else {
                        // 如果精确匹配未找到，尝试模糊匹配
                        String bestMatch = null;
                        Double bestMatchRent = null;

                        for (Map.Entry<String, Double> entry : utilityRentData.entrySet()) {
                            String rentName = entry.getKey();

                            // 模糊匹配：任一包含另一个
                            if (name.contains(rentName) || rentName.contains(name)) {
                                if (bestMatch == null || rentName.length() > bestMatch.length()) {
                                    // 选择最长的匹配（更可能是准确的）
                                    bestMatch = rentName;
                                    bestMatchRent = entry.getValue();
                                }
                            }
                        }

                        if (bestMatch != null) {
                            // 找到模糊匹配的水电房租数据，写入水电房租列
                            Cell urCell = row.getCell(utilityRentColIndex);
                            if (urCell == null) {
                                urCell = row.createCell(utilityRentColIndex);
                            }

                            // 使用数值格式写入
                            urCell.setCellType(CellType.NUMERIC);
                            urCell.setCellValue(bestMatchRent);

                            logger.info("成功写入模糊匹配水电房租值: " + bestMatchRent + " 到第" + (i+1) + "行第" +
                                    (utilityRentColIndex+1) + "列(水电房租)，姓名: " + name + " => 匹配: " + bestMatch);
                            matchCount++;
                        } else {
                            logger.warn("未找到姓名为 [" + name + "] 的水电房租数据");
                        }
                    }
                }

                logger.info("水电房租数据填写完成，共匹配 " + matchCount + " 条记录");
            }

            // 如果有考勤数据，遍历整个模板的姓名列，填写对应的加班数据
            if (!attendanceData.isEmpty() && (overtime1_5ColIndex != -1 || overtime2ColIndex != -1 || overtime3ColIndex != -1)) {
                logger.info("开始填写加班数据...");
                logger.info("加班列索引 - 1.5倍列索引: " + overtime1_5ColIndex +
                        " (第" + (overtime1_5ColIndex != -1 ? overtime1_5ColIndex+1 : -1) + "列), 2倍列索引: " + overtime2ColIndex +
                        " (第" + (overtime2ColIndex != -1 ? overtime2ColIndex+1 : -1) + "列), 3倍列索引: " + overtime3ColIndex +
                        " (第" + (overtime3ColIndex != -1 ? overtime3ColIndex+1 : -1) + "列)");

                // 确保加班列索引正确
                if (overtime1_5ColIndex == 0 && overtime2ColIndex > 0) {
                    logger.warn("1.5倍列索引为0，可能不正确，请检查是否被覆盖");
                }

                // 输出所有考勤数据的键值，便于调试
                for (Map.Entry<String, Map<String, Double>> entry : attendanceData.entrySet()) {
                    StringBuilder sb = new StringBuilder("考勤数据: " + entry.getKey() + " 包含字段: ");
                    for (String key : entry.getValue().keySet()) {
                        sb.append(key).append("=").append(entry.getValue().get(key)).append(", ");
                    }
                    logger.info(sb.toString());
                }

                // 统计匹配的考勤数据数量
                int matchCount = 0;

                // 从第3行开始（索引为2），前两行是表头
                int startRowIndex = 2;

                // 遍历模板中的所有行
                for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++) {
                    Row row = sheet.getRow(i);
                    if (row == null) continue;

                    // 获取姓名列
                    Cell nameCell = row.getCell(nameColIndex);
                    if (nameCell == null) continue;

                    String name = DataReader.getCellValueAsString(nameCell).trim();
                    if (name.isEmpty()) continue;

                    // 填写加班数据
                    Map<String, Double> personAttendance = attendanceData.get(name);
                    if (personAttendance != null) {
                        // 写入1.5倍加班数据
                        if (overtime1_5ColIndex != -1) {
                            // 使用更灵活的匹配逻辑，可能的键包括"1.5倍"、"1.5"、"1倍半"等
                            Double value = null;
                            for (String key : new String[]{"1.5倍", "1.5", "1倍半", "1"}) {
                                if (personAttendance.containsKey(key)) {
                                    value = personAttendance.get(key);
                                    logger.info("找到1.5倍加班数据，使用键: " + key + ", 值: " + value);
                                    break;
                                }
                            }

                            if (value != null) {
                                Cell overtimeCell = row.getCell(overtime1_5ColIndex);
                                if (overtimeCell == null) {
                                    overtimeCell = row.createCell(overtime1_5ColIndex);
                                }
                                overtimeCell.setCellValue(value);
                                logger.info("为" + name + "填写1.5倍加班数据: " + value);
                                matchCount++;
                            }
                        }

                        // 写入2倍加班数据
                        if (overtime2ColIndex != -1) {
                            Double value = null;
                            for (String key : new String[]{"2倍", "2", "2.0"}) {
                                if (personAttendance.containsKey(key)) {
                                    value = personAttendance.get(key);
                                    break;
                                }
                            }

                            if (value != null) {
                                Cell overtimeCell = row.getCell(overtime2ColIndex);
                                if (overtimeCell == null) {
                                    overtimeCell = row.createCell(overtime2ColIndex);
                                }
                                overtimeCell.setCellValue(value);
                                logger.info("为" + name + "填写2倍加班数据: " + value);
                            }
                        }

                        // 写入3倍加班数据
                        if (overtime3ColIndex != -1) {
                            Double value = null;
                            for (String key : new String[]{"3倍", "3", "3.0"}) {
                                if (personAttendance.containsKey(key)) {
                                    value = personAttendance.get(key);
                                    break;
                                }
                            }

                            if (value != null) {
                                Cell overtimeCell = row.getCell(overtime3ColIndex);
                                if (overtimeCell == null) {
                                    overtimeCell = row.createCell(overtime3ColIndex);
                                }
                                overtimeCell.setCellValue(value);
                                logger.info("为" + name + "填写3倍加班数据: " + value);
                            }
                        }
                    }
                }

                logger.info("加班数据填写完成，共匹配了" + matchCount + "条记录");
            }

            // 如果找到了基本工资列，填写固定值2080
            if (basicSalaryColIndex != -1) {
                logger.info("开始填写基本工资数据，固定值2080...");

                // 统计填写的记录数
                int fillCount = 0;

                // 从第3行开始（索引为2），前两行是表头
                int startRowIndex = 2;

                // 遍历模板中的所有行
                for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++) {
                    Row row = sheet.getRow(i);
                    if (row == null) continue;

                    // 获取姓名列
                    Cell nameCell = row.getCell(nameColIndex);
                    if (nameCell == null) continue;

                    String name = DataReader.getCellValueAsString(nameCell).trim();
                    if (name.isEmpty()) continue;

                    // 创建并填写基本工资单元格
                    Cell basicSalaryCell = row.getCell(basicSalaryColIndex);
                    if (basicSalaryCell == null) {
                        basicSalaryCell = row.createCell(basicSalaryColIndex);
                    }

                    // 设置固定值2080
                    basicSalaryCell.setCellValue(2080);
                    logger.info("为" + name + "填写基本工资: 2080");
                    fillCount++;
                }

                logger.info("基本工资数据填写完成，共填写了" + fillCount + "条记录");
            } else {
                logger.warn("未找到基本工资列，无法填写基本工资数据");
            }

            // 如果找到了基本工资列、加班时数列和加班工资列，计算并填写加班工资
            if (basicSalaryColIndex != -1 &&
                    (overtime1_5ColIndex != -1 || overtime2ColIndex != -1 || overtime3ColIndex != -1) &&
                    (weekdayOvertimeColIndex != -1 || weekendOvertimeColIndex != -1 || holidayOvertimeColIndex != -1)) {

                logger.info("开始计算并填写加班工资...");

                // 统计填写的记录数
                int calcCount = 0;

                // 从第3行开始（索引为2），前两行是表头
                int startRowIndex = 2;

                // 遍历模板中的所有行
                for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++) {
                    Row row = sheet.getRow(i);
                    if (row == null) continue;

                    // 获取姓名列
                    Cell nameCell = row.getCell(nameColIndex);
                    if (nameCell == null) continue;

                    String name = DataReader.getCellValueAsString(nameCell).trim();
                    if (name.isEmpty()) continue;

                    // 获取基本工资
                    Cell basicSalaryCell = row.getCell(basicSalaryColIndex);
                    if (basicSalaryCell == null) continue;

                    double basicSalary = DataReader.getCellValueAsDouble(basicSalaryCell);
                    if (basicSalary <= 0) {
                        logger.warn("员工 " + name + " 的基本工资为0或负数，跳过加班工资计算");
                        continue;
                    }

                    // 使用BigDecimal计算时薪，确保精度
                    BigDecimal bdBasicSalary = new BigDecimal(String.valueOf(basicSalary));
                    BigDecimal bdDays = new BigDecimal("21.75");
                    BigDecimal bdHours = new BigDecimal("8");

                    // 基本工资 / 21.75天 / 8小时，保留两位小数
                    BigDecimal bdHourlyRate = bdBasicSalary.divide(bdDays, 10, RoundingMode.HALF_UP)
                            .divide(bdHours, 10, RoundingMode.HALF_UP);
                    double hourlyRate = bdHourlyRate.setScale(2, RoundingMode.HALF_UP).doubleValue();

                    logger.info("员工 " + name + " 的基本工资为 " + basicSalary + "，计算得到时薪为 " + hourlyRate);

                    // 变量存储加班时长
                    double overtime1_5Hours = 0;
                    double overtime2Hours = 0;
                    double overtime3Hours = 0;

                    // 获取1.5倍加班时长
                    if (overtime1_5ColIndex != -1) {
                        Cell overtime1_5Cell = row.getCell(overtime1_5ColIndex);
                        if (overtime1_5Cell != null) {
                            overtime1_5Hours = DataReader.getCellValueAsDouble(overtime1_5Cell);
                        }
                    }

                    // 获取2倍加班时长
                    if (overtime2ColIndex != -1) {
                        Cell overtime2Cell = row.getCell(overtime2ColIndex);
                        if (overtime2Cell != null) {
                            overtime2Hours = DataReader.getCellValueAsDouble(overtime2Cell);
                        }
                    }

                    // 获取3倍加班时长
                    if (overtime3ColIndex != -1) {
                        Cell overtime3Cell = row.getCell(overtime3ColIndex);
                        if (overtime3Cell != null) {
                            overtime3Hours = DataReader.getCellValueAsDouble(overtime3Cell);
                        }
                    }

                    // 计算各类加班工资
                    BigDecimal bd1_5Rate = new BigDecimal("1.5");
                    BigDecimal bd2Rate = new BigDecimal("2.0");
                    BigDecimal bd3Rate = new BigDecimal("3.0");

                    BigDecimal bdOvertime1_5Hours = new BigDecimal(String.valueOf(overtime1_5Hours));
                    BigDecimal bdOvertime2Hours = new BigDecimal(String.valueOf(overtime2Hours));
                    BigDecimal bdOvertime3Hours = new BigDecimal(String.valueOf(overtime3Hours));

                    BigDecimal bdWeekdayOvertimePay = bdHourlyRate.multiply(bd1_5Rate).multiply(bdOvertime1_5Hours).setScale(2, RoundingMode.HALF_UP);
                    BigDecimal bdWeekendOvertimePay = bdHourlyRate.multiply(bd2Rate).multiply(bdOvertime2Hours).setScale(2, RoundingMode.HALF_UP);
                    BigDecimal bdHolidayOvertimePay = bdHourlyRate.multiply(bd3Rate).multiply(bdOvertime3Hours).setScale(2, RoundingMode.HALF_UP);

                    double weekdayOvertimePay = bdWeekdayOvertimePay.doubleValue();
                    double weekendOvertimePay = bdWeekendOvertimePay.doubleValue();
                    double holidayOvertimePay = bdHolidayOvertimePay.doubleValue();

                    // 输出更详细的计算过程
                    if (overtime1_5Hours > 0) {
                        logger.info("员工 " + name + " 的1.5倍加班工资计算过程: " +
                                "时薪(" + bdHourlyRate.setScale(2, RoundingMode.HALF_UP) +
                                ") × 1.5倍 × " + overtime1_5Hours + "小时 = " + weekdayOvertimePay);
                    }

                    if (overtime2Hours > 0) {
                        logger.info("员工 " + name + " 的2倍加班工资计算过程: " +
                                "时薪(" + bdHourlyRate.setScale(2, RoundingMode.HALF_UP) +
                                ") × 2.0倍 × " + overtime2Hours + "小时 = " + weekendOvertimePay);
                    }

                    if (overtime3Hours > 0) {
                        logger.info("员工 " + name + " 的3倍加班工资计算过程: " +
                                "时薪(" + bdHourlyRate.setScale(2, RoundingMode.HALF_UP) +
                                ") × 3.0倍 × " + overtime3Hours + "小时 = " + holidayOvertimePay);
                    }

                    // 写入平时加班工资
                    if (weekdayOvertimeColIndex != -1 && overtime1_5Hours > 0) {
                        Cell weekdayOvertimeCell = row.getCell(weekdayOvertimeColIndex);
                        if (weekdayOvertimeCell == null) {
                            weekdayOvertimeCell = row.createCell(weekdayOvertimeColIndex);
                        }
                        weekdayOvertimeCell.setCellValue(weekdayOvertimePay);
                        logger.info("为 " + name + " 填写平时加班工资: " + weekdayOvertimePay);
                    }

                    // 写入双休加班工资
                    if (weekendOvertimeColIndex != -1 && overtime2Hours > 0) {
                        Cell weekendOvertimeCell = row.getCell(weekendOvertimeColIndex);
                        if (weekendOvertimeCell == null) {
                            weekendOvertimeCell = row.createCell(weekendOvertimeColIndex);
                        }
                        weekendOvertimeCell.setCellValue(weekendOvertimePay);
                        logger.info("为 " + name + " 填写双休加班工资: " + weekendOvertimePay);
                    }

                    // 写入节日加班工资
                    if (holidayOvertimeColIndex != -1 && overtime3Hours > 0) {
                        Cell holidayOvertimeCell = row.getCell(holidayOvertimeColIndex);
                        if (holidayOvertimeCell == null) {
                            holidayOvertimeCell = row.createCell(holidayOvertimeColIndex);
                        }
                        holidayOvertimeCell.setCellValue(holidayOvertimePay);
                        logger.info("为 " + name + " 填写节日加班工资: " + holidayOvertimePay);
                    }

                    calcCount++;
                }

                logger.info("加班工资计算完成，共计算了 " + calcCount + " 条记录");
            } else {
                if (basicSalaryColIndex == -1) {
                    logger.warn("未找到基本工资列，无法计算加班工资");
                } else if (overtime1_5ColIndex == -1 && overtime2ColIndex == -1 && overtime3ColIndex == -1) {
                    logger.warn("未找到加班时数列，无法计算加班工资");
                } else {
                    logger.warn("未找到加班工资对应列，无法填写加班工资");
                }
            }

            // 根据系数表和考核得分计算总奖金
            if (coefficientTablePath != null && !coefficientTablePath.isEmpty() &&
                    assessmentScoreFilePath != null && !assessmentScoreFilePath.isEmpty()) {

                // 获取全局销售总量
                SalesInfo globalSalesInfo = GlobalDataStore.getSalaryInfo();
                double totalSalesQuantity = (globalSalesInfo != null) ? globalSalesInfo.getTotalSalesQuantity() : 0.0;

                logger.info("开始计算基于系数表和考核得分的总奖金，销售总量: " + totalSalesQuantity + "吨");

                // 从第3行开始（索引为2），前两行是表头
                int startRowIndex = 2;
                int bonusCalcCount = 0;

                // 遍历输出表格中的所有行
                for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++) {
                    Row row = sheet.getRow(i);
                    if (row == null) continue;

                    // 获取姓名
                    Cell nameCell = row.getCell(nameColIndex);
                    if (nameCell == null) continue;

                    String name = DataReader.getCellValueAsString(nameCell).trim();
                    if (name.isEmpty()) continue;

                    // 获取该员工的系数
                    Double coefficient = coefficientData.get(name);
                    if (coefficient == null || coefficient == 0) {
                        logger.info("员工 [" + name + "] 不在系数表中或系数为0，跳过总奖金计算");
                        continue;
                    }

                    // 获取考核得分
                    Double score = assessmentScoreData.get(name);
                    if (score == null) {
                        logger.info("员工 [" + name + "] 没有考核得分，使用默认分数100分");
                        score = 100.0; // 默认得分
                    }

                    // 计算总奖金
                    double scoreDecimal = score * 0.01; // 将百分制转为小数
                    double totalBonus = 0;

                    // 获取岗位列
                    String position = "";
                    if (positionColIndex != -1) {
                        Cell positionCell = row.getCell(positionColIndex);
                        if (positionCell != null) {
                            position = DataReader.getCellValueAsString(positionCell).trim();
                        }
                    }

                    // 卢东韬的特殊计算
                    if ("卢东韬".equals(name)) {
                        // 卢东韬的总奖金 = 系数表对应系数×总销量×考核分×0.01
                        totalBonus = coefficient * scoreDecimal * totalSalesQuantity;

                        // 将特殊处理（奖金预留和政策奖惩）的信息记录到日志，实际处理在后面进行
                        logger.info("卢东韬特殊处理：总奖金 = 系数(" + coefficient + ") * 考核得分(" +
                                score + " * 0.01 = " + scoreDecimal + ") * 总销量(" + totalSalesQuantity + ") = " + totalBonus +
                                ", 奖金预留固定为-1000元，政策奖惩加上10000元");
                    }
                    // 营销经理刘涛的特殊计算规则
                    else if ("刘涛".equals(name)) {
                        // 获取刘涛已有的总奖金
                        Cell bonusCell = row.getCell(totalBonusColIndex);
                        double currentBonus = 0;
                        if (bonusCell != null) {
                            currentBonus = DataReader.getCellValueAsDouble(bonusCell);
                        }

                        // 如果已有总奖金，则乘以考核分数百分比；否则按常规计算
                        if (currentBonus > 0) {
                            logger.info("营销经理 [" + name + "] 的特殊总奖金计算开始 - 已有总奖金: " + currentBonus);
                            logger.info("营销经理 [" + name + "] 的考核分数: " + score + ", 转为小数后: " + scoreDecimal);

                            // 确保计算正确
                            totalBonus = currentBonus * scoreDecimal;

                            logger.info("营销经理 [" + name + "] 的特殊总奖金计算: " + currentBonus + " * " +
                                    scoreDecimal + " = " + totalBonus);
                            logger.info("============ 刘涛特殊总奖金计算完成 ============");
                        } else {
                            totalBonus = coefficient * scoreDecimal * totalSalesQuantity;
                            logger.info("营销经理 [" + name + "] 尚无总奖金，使用常规计算: 系数(" + coefficient + ") * 考核得分(" +
                                    score + " * 0.01 = " + scoreDecimal + ") * 总销量(" + totalSalesQuantity + ") = " + totalBonus);
                        }
                    }
                    // 业务员、保安、保安队长、分切工、辅工、卡板工不使用该公式
                    else if (position.equals("业务员") || position.equals("保安") || position.equals("保安队长") ||
                            position.equals("分切工") || position.equals("辅工") || position.equals("卡板工")) {
                        // 保安和保安队长的处理在后面
                        if (position.equals("保安") || position.equals("保安队长")) {
                            logger.info("员工 [" + name + "] 是" + position + "，总奖金将在后续特殊处理");
                            continue;
                        } else {
                            // 其他特殊岗位暂不处理总奖金
                            logger.info("员工 [" + name + "] 是" + position + "，不使用系数×总销量×考核分的方式计算总奖金");
                            continue;
                        }
                    } else {
                        // 其他人的总奖金: 系数 * 考核得分 * 0.01 * 总销量
                        totalBonus = coefficient * scoreDecimal * totalSalesQuantity;
                        logger.info("计算员工 [" + name + "] 的总奖金: 系数(" + coefficient + ") * 考核得分(" +
                                score + " * 0.01 = " + scoreDecimal + ") * 总销量(" + totalSalesQuantity + ") = " + totalBonus);
                    }

                    // 四舍五入到两位小数
                    totalBonus = Math.round(totalBonus * 100) / 100.0;

                    // 写入总奖金列
                    Cell bonusCell = row.getCell(totalBonusColIndex);
                    if (bonusCell == null) {
                        bonusCell = row.createCell(totalBonusColIndex);
                    }

                    bonusCell.setCellType(CellType.NUMERIC);
                    bonusCell.setCellValue(totalBonus);

                    // 特别记录刘涛的最终奖金
                    if ("刘涛".equals(name)) {
                        logger.info("============ 最终写入刘涛的总奖金: " + totalBonus + " ============");
                    }

                    // 处理卢东韬的特殊情况：奖金预留固定为-1000元，政策奖惩固定为10000元加上原值
                    if ("卢东韬".equals(name)) {
                        // 处理奖金预留
                        if (bonusReserveColIndex != -1) {
                            Cell bonusReserveCell = row.getCell(bonusReserveColIndex);
                            if (bonusReserveCell == null) {
                                bonusReserveCell = row.createCell(bonusReserveColIndex);
                            }
                            bonusReserveCell.setCellType(CellType.NUMERIC);
                            bonusReserveCell.setCellValue(-1000.0); // 固定为-1000元
                            logger.info("卢东韬奖金预留固定为-1000元");
                        }

                        // 处理政策奖惩
                        if (policyRewardPenaltyColIndex != -1) {
                            Cell policyRewardCell = row.getCell(policyRewardPenaltyColIndex);
                            double originalValue = 0.0;
                            if (policyRewardCell != null) {
                                originalValue = DataReader.getCellValueAsDouble(policyRewardCell);
                            } else {
                                policyRewardCell = row.createCell(policyRewardPenaltyColIndex);
                            }
                            double newValue = 10000.0 + originalValue; // 固定加上10000元
                            policyRewardCell.setCellType(CellType.NUMERIC);
                            policyRewardCell.setCellValue(newValue);
                            logger.info("卢东韬政策奖惩从" + originalValue + "元调整为" + newValue + "元（增加10000元）");
                        }
                    }

                    bonusCalcCount++;
                }

                logger.info("基于系数表和考核得分的总奖金计算完成，共处理 " + bonusCalcCount + " 位员工");

                // 处理保安和保安队长的总奖金计算（固定200元*考核分*0.01）
                logger.info("开始处理保安和保安队长的总奖金计算");

                // 查找岗位列 - 使用前面已定义的岗位列索引
                int securityPositionColIndex = positionColIndex; // 使用已定义的变量

                // 如果之前未找到岗位列，则尝试在第1-3行查找
                if (securityPositionColIndex == -1) {
                    // 查找岗位列 - 通常在第1-3行的标题行中
                    for (int rowIndex = 0; rowIndex <= 2; rowIndex++) {
                        Row row = sheet.getRow(rowIndex);
                        if (row == null) continue;

                        for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                            Cell cell = row.getCell(colIndex);
                            if (cell == null) continue;

                            String cellValue = DataReader.getCellValueAsString(cell).trim();
                            if (cellValue.contains("岗位") || cellValue.equals("岗位") || cellValue.contains("职位") || cellValue.equals("职位")) {
                                securityPositionColIndex = colIndex;
                                logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到岗位列: " + cellValue);
                                break;
                            }
                        }
                        if (securityPositionColIndex != -1) break;
                    }
                }

                if (securityPositionColIndex != -1) {
                    int securityGuardCount = 0;

                    // 从第3行开始（索引为2），前两行是表头
                    for (int i = 2; i <= sheet.getLastRowNum(); i++) {
                        Row row = sheet.getRow(i);
                        if (row == null) continue;

                        // 获取岗位
                        Cell positionCell = row.getCell(securityPositionColIndex);
                        if (positionCell == null) continue;

                        String position = DataReader.getCellValueAsString(positionCell).trim();
                        if (position.equals("保安") || position.equals("保安队长")) {
                            // 获取姓名
                            Cell nameCell = row.getCell(nameColIndex);
                            if (nameCell == null) continue;

                            String name = DataReader.getCellValueAsString(nameCell).trim();
                            if (name.isEmpty()) continue;

                            // 获取考核得分
                            Double score = assessmentScoreData.get(name);
                            if (score == null) {
                                logger.info(position + " [" + name + "] 没有考核得分，跳过总奖金计算");
                                continue;
                            }

                            // 计算总奖金
                            double scoreDecimal = score * 0.01; // 将百分制转为小数
                            double securityBonus = 200.0 * scoreDecimal; // 固定200元*考核分百分比

                            // 四舍五入到两位小数
                            securityBonus = Math.round(securityBonus * 100) / 100.0;

                            logger.info(position + " [" + name + "] 的总奖金计算: 固定200元 * 考核得分(" +
                                    score + " * 0.01 = " + scoreDecimal + ") = " + securityBonus);

                            // 写入总奖金列
                            Cell bonusCell = row.getCell(totalBonusColIndex);
                            if (bonusCell == null) {
                                bonusCell = row.createCell(totalBonusColIndex);
                            }

                            bonusCell.setCellType(CellType.NUMERIC);
                            bonusCell.setCellValue(securityBonus);

                            securityGuardCount++;
                        }
                    }

                    logger.info("保安和保安队长总奖金计算完成，共处理 " + securityGuardCount + " 位员工");
                } else {
                    logger.warn("未找到岗位列，无法处理保安和保安队长的总奖金计算");
                }

                // 计算缺勤天数 = 应出勤 - 实际出勤，如果结果小于等于0则留空
                logger.info("开始计算缺勤天数");

                // 查找缺勤天数列和应出勤列
                int absentDaysColIndex = -1;
                int requiredAttendanceColIndex = -1;

                for (int rowIndex = 0; rowIndex <= 2; rowIndex++) {
                    Row row = sheet.getRow(rowIndex);
                    if (row == null) continue;

                    for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                        Cell cell = row.getCell(colIndex);
                        if (cell == null) continue;

                        String cellValue = DataReader.getCellValueAsString(cell).trim();
                        if (cellValue.contains("缺勤") || cellValue.equals("缺勤天数") || cellValue.equals("缺勤")) {
                            absentDaysColIndex = colIndex;
                            logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到缺勤天数列: " + cellValue);
                        } else if (cellValue.contains("应出勤") || cellValue.equals("应出勤")) {
                            requiredAttendanceColIndex = colIndex;
                            logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到应出勤列: " + cellValue);
                        }
                    }

                    // 如果两列都找到了，可以结束查找
                    if (absentDaysColIndex != -1 && requiredAttendanceColIndex != -1) break;
                }

                if (absentDaysColIndex != -1 && requiredAttendanceColIndex != -1) {
                    int processedCount = 0;

                    // 从第3行开始（索引为2），前两行是表头
                    for (int i = 2; i <= sheet.getLastRowNum(); i++) {
                        Row row = sheet.getRow(i);
                        if (row == null) continue;

                        // 获取姓名
                        Cell nameCell = row.getCell(nameColIndex);
                        if (nameCell == null) continue;

                        String name = DataReader.getCellValueAsString(nameCell).trim();
                        if (name.isEmpty()) continue;

                        // 特殊人员不参与考勤计算，缺勤天数留空
                        if (name.equals("卢明记") || name.equals("卢海金") || name.equals("袁海波") ||
                                name.equals("卢东韬") || name.equals("卢东经")) {
                            // 获取或创建缺勤天数单元格
                            Cell absentDaysCell = row.getCell(absentDaysColIndex);
                            if (absentDaysCell == null) {
                                absentDaysCell = row.createCell(absentDaysColIndex);
                            }
                            // 设置为空白
                            absentDaysCell.setCellType(CellType.BLANK);
                            logger.info("特殊人员 [" + name + "] 不参与考勤计算，缺勤天数留空");
                            processedCount++;
                            continue; // 跳过后续的考勤计算
                        }

                        // 获取应出勤天数
                        Cell requiredAttendanceCell = row.getCell(requiredAttendanceColIndex);
                        if (requiredAttendanceCell == null) continue;

                        double requiredAttendance = DataReader.getCellValueAsDouble(requiredAttendanceCell);

                        // 从考勤表数据中获取实际出勤天数
                        double actualAttendance = 0;
                        if (attendanceData.containsKey(name) && attendanceData.get(name).containsKey("实际出勤")) {
                            actualAttendance = attendanceData.get(name).get("实际出勤");
                        }

                        // 计算缺勤天数
                        double absentDays = requiredAttendance - actualAttendance;

                        // 获取或创建缺勤天数单元格
                        Cell absentDaysCell = row.getCell(absentDaysColIndex);
                        if (absentDaysCell == null) {
                            absentDaysCell = row.createCell(absentDaysColIndex);
                        }

                        // 如果缺勤天数小于等于0，留空；否则写入缺勤天数
                        if (absentDays <= 0) {
                            absentDaysCell.setCellType(CellType.BLANK);
                            logger.info("员工 [" + name + "] 无缺勤（应出勤: " + requiredAttendance +
                                    "，实际出勤: " + actualAttendance + "），缺勤天数单元格留空");
                        } else {
                            absentDaysCell.setCellType(CellType.NUMERIC);
                            absentDaysCell.setCellValue(absentDays);
                            logger.info("员工 [" + name + "] 的缺勤天数: 应出勤(" + requiredAttendance +
                                    ") - 实际出勤(" + actualAttendance + ") = " + absentDays);
                        }

                        processedCount++;
                    }

                    logger.info("缺勤天数计算完成，共处理 " + processedCount + " 位员工");
                } else {
                    if (absentDaysColIndex == -1) {
                        logger.warn("未找到缺勤天数列，无法计算缺勤天数");
                    }
                    if (requiredAttendanceColIndex == -1) {
                        logger.warn("未找到应出勤列，无法计算缺勤天数");
                    }
                }

                // 计算应发合计和实发合计
                logger.info("开始计算应发合计和实发合计");

                // 查找相关列索引
                int totalPayableColIndex = -1;        // 应发合计列
                int totalActualPayColIndex = -1;      // 实发合计列
                int weekdayOTColIndex = -1;           // 平时加班列
                int holidayOTColIndex = -1;           // 节日加班列
                int weekendOTColIndex = -1;           // 双休加班列
                int positionAllowanceColIndex = -1;   // 岗位津贴列
                int educationAllowanceColIndex = -1;  // 学历/其他津贴列
                int seniorityAllowanceColIndex = -1;  // 工龄津贴列
                int policyRewardColIndex = -1;        // 政策奖惩列
                int attendanceRewardColIndex = -1;    // 全勤/缺勤款列
                int otherPayableColIndex = -1;        // 其他（应发部分）列
                int housingFundColIndex = -1;         // 公积金列

                // 重用已经找到的列索引
                // basicSalaryColIndex, taxColIndex, socialInsuranceColIndex, utilityRentColIndex,
                // bonusReserveColIndex, rewardFundColIndex, otherDeductionColIndex 已在前面定义

                // 在表头中查找所有相关列
                for (int rowIndex = 0; rowIndex <= 2; rowIndex++) {
                    Row row = sheet.getRow(rowIndex);
                    if (row == null) continue;

                    for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                        Cell cell = row.getCell(colIndex);
                        if (cell == null) continue;

                        String cellValue = DataReader.getCellValueAsString(cell).trim();

                        // 查找应发合计列
                        if (cellValue.contains("应发合计") || cellValue.equals("应发合计")) {
                            totalPayableColIndex = colIndex;
                            logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到应发合计列: " + cellValue);
                        }
                        // 查找实发合计列
                        else if (cellValue.contains("实发合计") || cellValue.equals("实发合计")) {
                            totalActualPayColIndex = colIndex;
                            logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到实发合计列: " + cellValue);
                        }
                        // 查找基本工资列
                        else if (cellValue.contains("基本") || cellValue.equals("基本工资") || cellValue.equals("基本")) {
                            basicSalaryColIndex = colIndex;
                            logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到基本工资列: " + cellValue);
                        }
                        // 查找平时加班列
                        else if (cellValue.contains("平时加班") || cellValue.equals("平时加班")) {
                            weekdayOTColIndex = colIndex;
                            logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到平时加班列: " + cellValue);
                        }
                        // 查找节日加班列
                        else if (cellValue.contains("节日加班") || cellValue.equals("节日加班")) {
                            holidayOTColIndex = colIndex;
                            logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到节日加班列: " + cellValue);
                        }
                        // 查找双休加班列
                        else if (cellValue.contains("双休加班") || cellValue.equals("双休加班")) {
                            weekendOTColIndex = colIndex;
                            logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到双休加班列: " + cellValue);
                        }
                        // 查找岗位津贴列
                        else if (cellValue.contains("岗位津贴") || cellValue.equals("岗位津贴")) {
                            positionAllowanceColIndex = colIndex;
                            logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到岗位津贴列: " + cellValue);
                        }
                        // 查找学历/其他津贴列
                        else if (cellValue.contains("学历") || cellValue.contains("其他津贴")) {
                            educationAllowanceColIndex = colIndex;
                            logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到学历/其他津贴列: " + cellValue);
                        }
                        // 查找工龄津贴列
                        else if (cellValue.contains("工龄津贴") || cellValue.equals("工龄津贴")) {
                            seniorityAllowanceColIndex = colIndex;
                            logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到工龄津贴列: " + cellValue);
                        }
                        // 查找奖金预留列
                        else if (cellValue.contains("奖金预留") || cellValue.equals("奖金预留")) {
                            bonusReserveColIndex = colIndex;
                            logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到奖金预留列: " + cellValue);
                        }
                        // 查找奖励基金列
                        else if (cellValue.contains("奖励基金") || cellValue.equals("奖励基金")) {
                            rewardFundColIndex = colIndex;
                            logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到奖励基金列: " + cellValue);
                        }
                        // 查找政策奖惩列
                        else if (cellValue.contains("政策奖惩") || cellValue.equals("政策奖惩")) {
                            policyRewardColIndex = colIndex;
                            logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到政策奖惩列: " + cellValue);
                        }
                        // 查找全勤/缺勤款列
                        else if (cellValue.contains("全勤") || cellValue.contains("缺勤款")) {
                            attendanceRewardColIndex = colIndex;
                            logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到全勤/缺勤款列: " + cellValue);
                        }
                        // 查找其他（应发部分）列
                        else if (cellValue.equals("其他") && colIndex < totalPayableColIndex && totalPayableColIndex != -1) {
                            otherPayableColIndex = colIndex;
                            logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到其他（应发部分）列: " + cellValue);
                        }
                        // 查找个税列
                        else if (cellValue.contains("个税") || cellValue.equals("个税")) {
                            taxColIndex = colIndex;
                            logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到个税列: " + cellValue);
                        }
                        // 查找社保列
                        else if (cellValue.contains("社保") || cellValue.equals("社保")) {
                            socialInsuranceColIndex = colIndex;
                            logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到社保列: " + cellValue);
                        }
                        // 查找公积金列
                        else if (cellValue.contains("公积金") || cellValue.equals("公积金")) {
                            housingFundColIndex = colIndex;
                            logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到公积金列: " + cellValue);
                        }
                        // 查找水电房租列
                        else if (cellValue.contains("水电房租") || cellValue.equals("水电房租")) {
                            utilityRentColIndex = colIndex;
                            logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到水电房租列: " + cellValue);
                        }
                        // 查找其他（应扣部分）列
                        else if (cellValue.equals("其他") && totalPayableColIndex != -1 && colIndex > totalPayableColIndex) {
                            otherDeductionColIndex = colIndex;
                            logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到其他（应扣部分）列: " + cellValue);
                        }
                    }
                }

                // 计算全勤奖和迟到扣款
                logger.info("开始计算全勤奖和迟到扣款");

                // 查找全勤/缺勤款列
                int attendanceAwardColIndex = -1;

                for (int rowIndex = 0; rowIndex <= 2; rowIndex++) {
                    Row row = sheet.getRow(rowIndex);
                    if (row == null) continue;

                    for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                        Cell cell = row.getCell(colIndex);
                        if (cell == null) continue;

                        String cellValue = DataReader.getCellValueAsString(cell).trim();
                        if (cellValue.contains("全勤") || cellValue.contains("缺勤款") ||
                                cellValue.equals("全勤/缺勤") || cellValue.equals("全勤/缺勤款")) {
                            attendanceAwardColIndex = colIndex;
                            logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到全勤/缺勤款列: " + cellValue);
                            break;
                        }
                    }

                    if (attendanceAwardColIndex != -1) break; // 如果找到了，就退出循环
                }

                if (attendanceAwardColIndex != -1) {
                    int processedCount = 0;

                    // 从第3行开始（索引为2），前两行是表头
                    for (int i = 2; i <= sheet.getLastRowNum(); i++) {
                        Row row = sheet.getRow(i);
                        if (row == null) continue;

                        // 获取姓名
                        Cell nameCell = row.getCell(nameColIndex);
                        if (nameCell == null) continue;

                        String name = DataReader.getCellValueAsString(nameCell).trim();
                        if (name.isEmpty()) continue;

                        // 获取或创建全勤/缺勤款单元格，获取现有值（可能已有其他逻辑计算的值）
                        Cell attendanceAwardCell = row.getCell(attendanceAwardColIndex);
                        double existingValue = 0.0;
                        if (attendanceAwardCell == null) {
                            attendanceAwardCell = row.createCell(attendanceAwardColIndex);
                        } else {
                            existingValue = DataReader.getCellValueAsDouble(attendanceAwardCell);
                        }

                        // 检查考勤表中的病假、带薪假、事假、旷工记录
                        double sickLeave = 0.0;
                        double paidLeave = 0.0;
                        double personalLeave = 0.0;
                        double absenteeism = 0.0;
                        boolean hasAbsentDays = false;

                        // 从考勤表数据中获取各种假期记录
                        if (attendanceData.containsKey(name)) {
                            Map<String, Double> personAttendance = attendanceData.get(name);

                            if (personAttendance.containsKey("病假")) {
                                sickLeave = personAttendance.get("病假");
                            }

                            if (personAttendance.containsKey("带薪假")) {
                                paidLeave = personAttendance.get("带薪假");
                            }

                            if (personAttendance.containsKey("事假")) {
                                personalLeave = personAttendance.get("事假");
                            }

                            if (personAttendance.containsKey("旷工")) {
                                absenteeism = personAttendance.get("旷工");
                            }
                        }

                        // 累计缺勤类型的天数（不包括带薪假）
                        double totalAbsentDays = sickLeave + personalLeave + absenteeism;
                        hasAbsentDays = totalAbsentDays > 0;

                        // 记录带薪假单独信息，但不计入缺勤天数
                        if (paidLeave > 0) {
                            logger.info("员工 [" + name + "] 有带薪假 " + paidLeave + " 天，但不计入缺勤天数");
                        }

                        // 更新缺勤天数列
                        if (absentDaysColIndex != -1) {
                            Cell absentDaysCell = row.getCell(absentDaysColIndex);
                            if (absentDaysCell == null) {
                                absentDaysCell = row.createCell(absentDaysColIndex);
                            }

                            // 特殊人员不计算缺勤天数
                            if (name.equals("卢明记") || name.equals("卢海金") || name.equals("袁海波") ||
                                    name.equals("卢东韬") || name.equals("卢东经")) {
                                // 清空特殊人员的缺勤天数单元格
                                absentDaysCell.setCellType(CellType.BLANK);
                                logger.info("特殊人员 [" + name + "] 不计算缺勤天数，单元格留空");
                            }
                            // 如果有缺勤，写入累计的缺勤天数
                            else if (totalAbsentDays > 0) {
                                absentDaysCell.setCellType(CellType.NUMERIC);
                                absentDaysCell.setCellValue(totalAbsentDays);
                                logger.info("员工 [" + name + "] 缺勤天数更新为: " + totalAbsentDays);
                            }
                            // 如果没有缺勤，清空单元格
                            else {
                                absentDaysCell.setCellType(CellType.BLANK);
                                logger.info("员工 [" + name + "] 无缺勤记录，缺勤天数单元格留空");
                            }
                        }

                        if (hasAbsentDays) {
                            StringBuilder absentDetails = new StringBuilder(name + " 缺勤明细(不含带薪假): ");
                            if (sickLeave > 0) absentDetails.append("病假(" + sickLeave + "天) ");
                            if (personalLeave > 0) absentDetails.append("事假(" + personalLeave + "天) ");
                            if (absenteeism > 0) absentDetails.append("旷工(" + absenteeism + "天) ");
                            absentDetails.append("总计: " + totalAbsentDays + "天");
                            logger.info(absentDetails.toString());
                        }

                        // 检查迟到次数
                        int lateCount = 0;
                        if (attendanceData.containsKey(name) && attendanceData.get(name).containsKey("迟到次数")) {
                            lateCount = attendanceData.get(name).get("迟到次数").intValue();
                            logger.info("员工 [" + name + "] 迟到次数: " + lateCount);
                        }

                        double attendanceAdjustment = 0.0;

                        // 特殊人员不计算全勤奖
                        boolean isSpecialPerson = name.equals("卢明记") || name.equals("卢海金") ||
                                name.equals("袁海波") || name.equals("卢东韬");

                        if (isSpecialPerson) {
                            logger.info("员工 [" + name + "] 属于特殊人员，不计算全勤奖");
                        }
                        // 计算全勤奖：如果没有缺勤且没有迟到，可以得到30元全勤奖
                        else if (!hasAbsentDays && lateCount == 0) {
                            attendanceAdjustment += 30.0;
                            logger.info("员工 [" + name + "] 满足全勤条件，获得30元全勤奖");
                        }

                        // 计算迟到扣款：第一次扣10元，第二次10元，第三次80元，第四次50元，第五次及以后每次50元
                        if (lateCount > 0) {
                            double lateFine = 0.0;
                            if (lateCount >= 1) lateFine += 10.0;
                            if (lateCount >= 2) lateFine += 10.0;
                            if (lateCount >= 3) lateFine += 80.0;
                            if (lateCount >= 4) lateFine += 50.0;
                            if (lateCount >= 5) lateFine += (lateCount - 4) * 50.0;

                            attendanceAdjustment -= lateFine;
                            logger.info("员工 [" + name + "] 迟到" + lateCount + "次，扣款" + lateFine + "元");
                        }

                        // 计算缺勤扣款，处理病假、事假和旷工（旷工按3倍事假天数计算）
                        if (totalAbsentDays > 0 || absenteeism > 0) {
                            // 获取基本工资
                            double basicSalary = 0.0;
                            if (basicSalaryColIndex != -1) {
                                Cell basicCell = row.getCell(basicSalaryColIndex);
                                if (basicCell != null) {
                                    basicSalary = DataReader.getCellValueAsDouble(basicCell);
                                }
                            }

                            // 获取岗位津贴
                            double positionAllowance = 0.0;
                            if (positionAllowanceColIndex != -1) {
                                Cell positionCell = row.getCell(positionAllowanceColIndex);
                                if (positionCell != null) {
                                    positionAllowance = DataReader.getCellValueAsDouble(positionCell);
                                }
                            }

                            // 实际扣款计算中，将旷工转为事假天数（1天旷工=3天事假）
                            double effectivePersonalLeave = personalLeave + (absenteeism * 3);
                            double totalLeave = effectivePersonalLeave + sickLeave;

                            // 应出勤天数，默认为21.75天，如果有其他值则使用实际值
                            double requiredAttendance = 21.75;
                            if (requiredAttendanceColIndex != -1) {
                                Cell requiredAttendanceCell = row.getCell(requiredAttendanceColIndex);
                                if (requiredAttendanceCell != null) {
                                    double cellValue = DataReader.getCellValueAsDouble(requiredAttendanceCell);
                                    if (cellValue > 0) {
                                        requiredAttendance = cellValue;
                                    }
                                }
                            }

                            double absentFine = 0.0;

                            // 获取平时加班
                            double weekdayOT = 0.0;
                            if (weekdayOTColIndex != -1) {
                                Cell cell = row.getCell(weekdayOTColIndex);
                                if (cell != null) {
                                    weekdayOT = DataReader.getCellValueAsDouble(cell);
                                }
                            }

                            // 获取节日加班
                            double holidayOT = 0.0;
                            if (holidayOTColIndex != -1) {
                                Cell cell = row.getCell(holidayOTColIndex);
                                if (cell != null) {
                                    holidayOT = DataReader.getCellValueAsDouble(cell);
                                }
                            }

                            // 获取双休加班
                            double weekendOT = 0.0;
                            if (weekendOTColIndex != -1) {
                                Cell cell = row.getCell(weekendOTColIndex);
                                if (cell != null) {
                                    weekendOT = DataReader.getCellValueAsDouble(cell);
                                }
                            }

                            // 获取总奖金
                            double totalBonus = 0.0;
                            if (totalBonusColIndex != -1) {
                                Cell cell = row.getCell(totalBonusColIndex);
                                if (cell != null) {
                                    totalBonus = DataReader.getCellValueAsDouble(cell);
                                }
                            }

                            // 获取学历/其他津贴
                            double educationAllowance = 0.0;
                            if (educationAllowanceColIndex != -1) {
                                Cell cell = row.getCell(educationAllowanceColIndex);
                                if (cell != null) {
                                    educationAllowance = DataReader.getCellValueAsDouble(cell);
                                }
                            }

                            // 获取工龄津贴
                            double seniorityAllowance = 0.0;
                            if (seniorityAllowanceColIndex != -1) {
                                Cell cell = row.getCell(seniorityAllowanceColIndex);
                                if (cell != null) {
                                    seniorityAllowance = DataReader.getCellValueAsDouble(cell);
                                }
                            }

                            // 1. 请假7天及以下
                            if (totalLeave <= 7) {
                                // 计算日薪 - 对于请假7天及以下，只计算基本工资和岗位津贴
                                double dailyRate = (basicSalary + positionAllowance) / requiredAttendance;

                                // 计算事假扣款
                                double personalLeaveFine = dailyRate * effectivePersonalLeave;
                                // 计算病假扣款（只扣20%）
                                double sickLeaveFine = dailyRate * sickLeave * 0.2;

                                absentFine = personalLeaveFine + sickLeaveFine;

                                logger.info("员工 [" + name + "] 请假天数(" + totalLeave + ")<=7天，" +
                                        "缺勤款 = (基本工资" + basicSalary + "+岗位津贴" + positionAllowance +
                                        ")÷应出勤天数" + requiredAttendance + "×(事假" + effectivePersonalLeave +
                                        " + 病假" + sickLeave + "×0.2) = " + absentFine);
                            }
                            // 2. 请假7天以上
                            else {
                                // 计算全部薪资
                                double totalSalary = basicSalary + weekdayOT + holidayOT + weekendOT +
                                        totalBonus + positionAllowance + educationAllowance +
                                        seniorityAllowance;

                                double dailyRate = totalSalary / requiredAttendance;

                                // 计算事假扣款
                                double personalLeaveFine = dailyRate * effectivePersonalLeave;
                                // 计算病假扣款（只扣20%）
                                double sickLeaveFine = dailyRate * sickLeave * 0.2;

                                absentFine = personalLeaveFine + sickLeaveFine;

                                logger.info("员工 [" + name + "] 请假天数(" + totalLeave + ")>7天，" +
                                        "缺勤款 = 全部薪资(" + totalSalary +
                                        ")÷应出勤天数" + requiredAttendance +
                                        "×(事假" + effectivePersonalLeave + " + 病假" + sickLeave + "×0.2) = " + absentFine);
                            }

                            // 将缺勤扣款设为负数
                            attendanceAdjustment -= absentFine;
                            logger.info("员工 [" + name + "] 缺勤扣款: " + absentFine + "元，全勤/缺勤款调整: " + attendanceAdjustment);
                        }

                        // 合并现有值和新计算的调整值
                        double totalAttendanceAdjustment = existingValue + attendanceAdjustment;

                        // 四舍五入到两位小数
                        totalAttendanceAdjustment = Math.round(totalAttendanceAdjustment * 100) / 100.0;

                        // 写入结果
                        attendanceAwardCell.setCellType(CellType.NUMERIC);
                        attendanceAwardCell.setCellValue(totalAttendanceAdjustment);

                        if (existingValue != 0.0) {
                            logger.info("员工 [" + name + "] 全勤/缺勤款计算: 现有值(" + existingValue +
                                    ") + 全勤奖和迟到调整(" + attendanceAdjustment + ") = " + totalAttendanceAdjustment);
                        } else {
                            logger.info("员工 [" + name + "] 全勤/缺勤款计算结果: " + totalAttendanceAdjustment);
                        }

                        processedCount++;
                    }

                    logger.info("全勤奖和迟到扣款计算完成，共处理 " + processedCount + " 位员工");
                } else {
                    logger.warn("未找到全勤/缺勤款列，无法计算全勤奖和迟到扣款");
                }

                // 额外处理刘涛的总奖金计算（在处理完其他特殊情况后）
                logger.info("开始单独处理刘涛的总奖金计算");

                // 从考核得分表中获取刘涛的分数
                if (!assessmentScoreData.containsKey("刘涛")) {
                    logger.info("未找到刘涛的考核得分，无法计算其总奖金");
                    return outputPath; // 如果找不到刘涛的考核分数，直接返回
                }

                double liutaoScore = assessmentScoreData.get("刘涛");
                logger.info("找到刘涛的考核得分: " + liutaoScore);
                double liutaoScoreDecimal = liutaoScore * 0.01; // 转为小数

                // 查找刘涛并处理
                for (int i = 2; i <= sheet.getLastRowNum(); i++) {
                    Row row = sheet.getRow(i);
                    if (row == null) continue;

                    Cell nameCell = row.getCell(nameColIndex);
                    if (nameCell == null) continue;

                    String name = DataReader.getCellValueAsString(nameCell).trim();
                    if ("刘涛".equals(name)) {
                        logger.info("找到刘涛，开始计算特殊总奖金");

                        // 获取刘涛现有的总奖金
                        Cell bonusCell = row.getCell(totalBonusColIndex);
                        double currentBonus = 0;
                        if (bonusCell != null) {
                            currentBonus = DataReader.getCellValueAsDouble(bonusCell);
                            logger.info("刘涛当前总奖金: " + currentBonus);
                        } else {
                            bonusCell = row.createCell(totalBonusColIndex);
                            logger.info("刘涛没有总奖金单元格，已创建");
                        }

                        // 只有当刘涛有现有总奖金时才进行计算
                        if (currentBonus <= 0) {
                            logger.info("刘涛没有现有总奖金，无法进行特殊计算");
                            continue; // 如果没有现有总奖金，跳过该操作
                        }

                        // 计算新的总奖金：现有总奖金 * 考核分数百分比
                        double newBonus = currentBonus * liutaoScoreDecimal;
                        logger.info("刘涛特殊总奖金计算: " + currentBonus + " * " + liutaoScoreDecimal + " = " + newBonus);

                        // 四舍五入到两位小数
                        newBonus = Math.round(newBonus * 100) / 100.0;

                        // 写入计算结果
                        bonusCell.setCellType(CellType.NUMERIC);
                        bonusCell.setCellValue(newBonus);
                        logger.info("已将刘涛的特殊总奖金 " + newBonus + " 写入表格");
                        break; // 找到刘涛并处理后退出循环
                    }
                }

                // 如果找到了应发合计列和实发合计列，进行计算
                if (totalPayableColIndex != -1 && totalActualPayColIndex != -1) {
                    int processedCount = 0;

                    // 从第3行开始（索引为2），前两行是表头
                    for (int i = 2; i <= sheet.getLastRowNum(); i++) {
                        Row row = sheet.getRow(i);
                        if (row == null) continue;

                        // 获取姓名
                        Cell nameCell = row.getCell(nameColIndex);
                        if (nameCell == null) continue;

                        String name = DataReader.getCellValueAsString(nameCell).trim();
                        if (name.isEmpty()) continue;

                        // 初始化应发项目的合计金额
                        double totalPayable = 0.0;
                        StringBuilder payableComponents = new StringBuilder(name + " 应发合计组成: ");

                        // 累加各应发项目
                        // 基本工资
                        if (basicSalaryColIndex != -1) {
                            Cell cell = row.getCell(basicSalaryColIndex);
                            if (cell != null) {
                                double value = DataReader.getCellValueAsDouble(cell);
                                totalPayable += value;
                                if (value != 0) payableComponents.append("基本工资(" + value + ") + ");
                            }
                        }

                        // 平时加班
                        if (weekdayOTColIndex != -1) {
                            Cell cell = row.getCell(weekdayOTColIndex);
                            if (cell != null) {
                                double value = DataReader.getCellValueAsDouble(cell);
                                totalPayable += value;
                                if (value != 0) payableComponents.append("平时加班(" + value + ") + ");
                            }
                        }

                        // 节日加班
                        if (holidayOTColIndex != -1) {
                            Cell cell = row.getCell(holidayOTColIndex);
                            if (cell != null) {
                                double value = DataReader.getCellValueAsDouble(cell);
                                totalPayable += value;
                                if (value != 0) payableComponents.append("节日加班(" + value + ") + ");
                            }
                        }

                        // 双休加班
                        if (weekendOTColIndex != -1) {
                            Cell cell = row.getCell(weekendOTColIndex);
                            if (cell != null) {
                                double value = DataReader.getCellValueAsDouble(cell);
                                totalPayable += value;
                                if (value != 0) payableComponents.append("双休加班(" + value + ") + ");
                            }
                        }

                        // 总奖金
                        if (totalBonusColIndex != -1) {
                            Cell cell = row.getCell(totalBonusColIndex);
                            if (cell != null) {
                                double value = DataReader.getCellValueAsDouble(cell);
                                totalPayable += value;
                                if (value != 0) payableComponents.append("总奖金(" + value + ") + ");
                            }
                        }

                        // 岗位津贴
                        if (positionAllowanceColIndex != -1) {
                            Cell cell = row.getCell(positionAllowanceColIndex);
                            if (cell != null) {
                                double value = DataReader.getCellValueAsDouble(cell);
                                totalPayable += value;
                                if (value != 0) payableComponents.append("岗位津贴(" + value + ") + ");
                            }
                        }

                        // 学历/其他津贴
                        if (educationAllowanceColIndex != -1) {
                            Cell cell = row.getCell(educationAllowanceColIndex);
                            if (cell != null) {
                                double value = DataReader.getCellValueAsDouble(cell);
                                totalPayable += value;
                                if (value != 0) payableComponents.append("学历/其他津贴(" + value + ") + ");
                            }
                        }

                        // 工龄津贴
                        if (seniorityAllowanceColIndex != -1) {
                            Cell cell = row.getCell(seniorityAllowanceColIndex);
                            if (cell != null) {
                                double value = DataReader.getCellValueAsDouble(cell);
                                totalPayable += value;
                                if (value != 0) payableComponents.append("工龄津贴(" + value + ") + ");
                            }
                        }

                        // 奖金预留
                        if (bonusReserveColIndex != -1) {
                            Cell cell = row.getCell(bonusReserveColIndex);
                            if (cell != null) {
                                double value = DataReader.getCellValueAsDouble(cell);
                                totalPayable += value;
                                if (value != 0) payableComponents.append("奖金预留(" + value + ") + ");
                            }
                        }

                        // 奖励基金
                        if (rewardFundColIndex != -1) {
                            Cell cell = row.getCell(rewardFundColIndex);
                            if (cell != null) {
                                double value = DataReader.getCellValueAsDouble(cell);
                                totalPayable += value;
                                if (value != 0) payableComponents.append("奖励基金(" + value + ") + ");
                            }
                        }

                        // 政策奖惩
                        if (policyRewardColIndex != -1) {
                            Cell cell = row.getCell(policyRewardColIndex);
                            if (cell != null) {
                                double value = DataReader.getCellValueAsDouble(cell);
                                totalPayable += value;
                                if (value != 0) payableComponents.append("政策奖惩(" + value + ") + ");
                            }
                        }

                        // 全勤/缺勤款
                        if (attendanceRewardColIndex != -1) {
                            Cell cell = row.getCell(attendanceRewardColIndex);
                            if (cell != null) {
                                double value = DataReader.getCellValueAsDouble(cell);
                                totalPayable += value;
                                if (value != 0) {
                                    payableComponents.append("全勤/缺勤款(" + value + ") + ");
                                    logger.info("员工 [" + name + "] 的全勤/缺勤款(" + value + ")已计入应发合计");
                                }
                            } else {
                                logger.warn("员工 [" + name + "] 的全勤/缺勤款单元格为空");
                            }
                        } else {
                            logger.warn("未找到全勤/缺勤款列，无法将其计入应发合计");
                        }

                        // 其他（应发部分）
                        if (otherPayableColIndex != -1) {
                            Cell cell = row.getCell(otherPayableColIndex);
                            if (cell != null) {
                                double value = DataReader.getCellValueAsDouble(cell);
                                totalPayable += value;
                                if (value != 0) payableComponents.append("其他应发项(" + value + ") + ");
                            }
                        }

                        // 四舍五入到两位小数
                        totalPayable = Math.round(totalPayable * 100) / 100.0;

                        // 写入应发合计
                        Cell totalPayableCell = row.getCell(totalPayableColIndex);
                        if (totalPayableCell == null) {
                            totalPayableCell = row.createCell(totalPayableColIndex);
                        }
                        totalPayableCell.setCellType(CellType.NUMERIC);
                        totalPayableCell.setCellValue(totalPayable);

                        // 从应发合计中扣除各项目以计算实发合计
                        double totalActualPay = totalPayable;
                        StringBuilder deductionComponents = new StringBuilder(name + " 扣除项目: ");

                        // 个税
                        if (taxColIndex != -1) {
                            Cell cell = row.getCell(taxColIndex);
                            if (cell != null) {
                                double value = DataReader.getCellValueAsDouble(cell);
                                totalActualPay -= value;
                                if (value != 0) deductionComponents.append("个税(" + value + ") + ");
                            }
                        }

                        // 社保
                        if (socialInsuranceColIndex != -1) {
                            Cell cell = row.getCell(socialInsuranceColIndex);
                            if (cell != null) {
                                double value = DataReader.getCellValueAsDouble(cell);
                                totalActualPay -= value;
                                if (value != 0) deductionComponents.append("社保(" + value + ") + ");
                            }
                        }

                        // 公积金
                        if (housingFundColIndex != -1) {
                            Cell cell = row.getCell(housingFundColIndex);
                            if (cell != null) {
                                double value = DataReader.getCellValueAsDouble(cell);
                                totalActualPay -= value;
                                if (value != 0) deductionComponents.append("公积金(" + value + ") + ");
                            }
                        }

                        // 水电房租
                        if (utilityRentColIndex != -1) {
                            Cell cell = row.getCell(utilityRentColIndex);
                            if (cell != null) {
                                double value = DataReader.getCellValueAsDouble(cell);
                                totalActualPay -= value;
                                if (value != 0) deductionComponents.append("水电房租(" + value + ") + ");
                            }
                        }

                        // 其他（应扣部分）
                        if (otherDeductionColIndex != -1) {
                            Cell cell = row.getCell(otherDeductionColIndex);
                            if (cell != null) {
                                double value = DataReader.getCellValueAsDouble(cell);
                                totalActualPay -= value;
                                if (value != 0) deductionComponents.append("其他应扣项(" + value + ") + ");
                            }
                        }

                        // 四舍五入到两位小数
                        totalActualPay = Math.round(totalActualPay * 100) / 100.0;

                        // 写入实发合计
                        Cell totalActualPayCell = row.getCell(totalActualPayColIndex);
                        if (totalActualPayCell == null) {
                            totalActualPayCell = row.createCell(totalActualPayColIndex);
                        }
                        totalActualPayCell.setCellType(CellType.NUMERIC);
                        totalActualPayCell.setCellValue(totalActualPay);

                        // 记录日志
                        if (payableComponents.length() > name.length() + 10) {
                            // 去掉最后的" + "
                            logger.info(payableComponents.substring(0, payableComponents.length() - 3) + " = " + totalPayable);
                        } else {
                            logger.info(name + " 应发合计: " + totalPayable);
                        }

                        if (deductionComponents.length() > name.length() + 10) {
                            // 去掉最后的" + "
                            logger.info(deductionComponents.substring(0, deductionComponents.length() - 3));
                        }

                        logger.info(name + " 实发合计: " + totalActualPay);

                        processedCount++;
                    }

                    logger.info("应发合计和实发合计计算完成，共处理 " + processedCount + " 位员工");
                } else {
                    if (totalPayableColIndex == -1) {
                        logger.warn("未找到应发合计列，无法计算应发合计");
                    }
                    if (totalActualPayColIndex == -1) {
                        logger.warn("未找到实发合计列，无法计算实发合计");
                    }
                }




            } else {
                logger.info("未提供系数表或考核得分表，跳过总奖金计算");
            }

            // 写入文件
            try (FileOutputStream fos = new FileOutputStream(outputPath)) {
                workbook.write(fos);
                logger.info("成功写入Excel文件: " + outputPath);
            }

            return outputPath;

        } catch (IOException e) {
            logger.error("写入Excel文件失败: " + e.getMessage(), e);
            return null;
        }
    }

}
